SQL Server

【SQL Server】文字列の先頭から改行コードまでの文字を取得するSQL文

SQLの文法

長文が格納されている文字列から、最初の改行までの一文を取得するSQLが必要になりました。

事前に文字列の改行コードとして何が入っているのか調べる

そもそも元となるデータの改行コードには何が入っているのでしょうか?

改行コードには2種類(+組み合わせ)あります。

名称 ASCIIコード
キャリッジリターン(CR) char(13)
ラインフィード(LF) char(10)
キャリッジリターン+ラインフィード(CRLF) char(13)+char(10)

今回のデータは値を入力するインターフェイスがあるシステムを使って入力されています。

エンドユーザーは基本的には手入力しますが、場合によっては文字をコピペして貼り付ける人もいるかもしれません。

となると、全データが同じ改行コードでセットされているという保証がありません。

とりあえず取得した元データをサンプリングしてSAKURAテキストエディタに貼り付けてみました。

どうやら主にCRLF(キャリッジリターン+ラインフィード)がセットされているようです。

念のため、以下のSQL文で該当するデータが検索できるか調べてみます。

  • SELECT count(備考) FROM テーブル名 where 備考 like ‘%’ + nchar(10) + ‘%’
  • SELECT count(備考) FROM テーブル名 where 備考 like ‘%’ + nchar(13) + ‘%’
  • SELECT count(備考) FROM テーブル名 where 備考 like ‘%’ + nchar(13) + nchar(10) + ‘%’

備考はnvarchar型で定義されているので、char()ではなくnchar()を使います。

件数を確認したところ、一部CR(キャリッジリターン)のみのデータがあるようです。

ということで、Char(13)の桁位置を取得できれば、すべてのデータで改行までの文字を切り出せそうです。

CR(キャリッジリターン)の桁位置を取得する

SELECT CHARINDEX(備考, NCHAR(13)) FROM テーブル名

CHARINDEX()関数を利用して、改行コード(CR)の出現桁位置を取得します。

備考から最初の改行コードまでの文字列を取得する

SELECT LEFT(備考, CHARINDEX(備考, NCHAR(13)-1) FROM テーブル名

このとき、改行コード(CR)の初回出現桁位置は9。
切り取る文字は左から8文字(9-1)です。

これで、「ここまでが1行目」の文字のみ取得できました。

ABOUT ME
アズビーパートナーズ
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。