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)の出現桁位置を取得します。

CHARINDEXの構文

CHARINDEX( 検索文字列, 検索対象文字列[, 位置 ] )

検索対象文字列データから検索文字列を検索します。

戻り値は検索対象文字列の中に検索文字列が含まれる場合、最初に一致した文字列開始位置です。

オプションで位置を指定した場合は検索文字列の指定した位置以降で最初の一致を探します。

※検索文字列が見つからなかった場合は戻り値として0が返されます。

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

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

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

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

この構文の場合、備考文字列に改行コードが含まれなかった場合、

LEFT(備考, -1)となり「537:LEFT 関数または SUBSTRING 関数に渡した長さのパラメーターが無効です。」というエラーとなります。

エラーにならないためには0の時はLEFT関数を使わない制御が必要です。

IIF文やCASE文を使って制御しましょう。

IIF(CHARINDEX(NCHAR(13),[備考])=0,[備考],LEFT([備考], CHARINDEX(NCHAR(13),[備考]) -1))

SQLの文法
【SQL Server】SQL文の中で条件分岐させる2つの方法SQLSeverで条件分岐を記述する方法です。CASE文を使う方法とIIF文を使う方法がありますので状況に応じてご利用ください。...
ABOUT ME
アズビーパートナーズ
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。
今のスキルのままで大丈夫?

あなたのスキル。今のままで大丈夫ですか?

時代のニーズに合った開発スキルを身につけてあなたの価値を高めましょう。

\オンラインでも教室でも学べるおすすめのスクール/

TECH::CAMP(エンジニアスクール)

まずは基礎から・・・という方はProgateUdemyどっとインストールなどがおすすめ!

スキルアップについて詳しくはこちら