データ抽出時につい調べてしまうSQL構文

■やりたいこと

1．抽出したデータをそのままエクセル等に貼り付けした際に、文字列に改行が入っていると行がずれてしまうことがあるので、改行コードを削除して抽出。

2．特定の区切文字を含んだ文字列を、区切文字で分割して抽出。（例：A|B|C）

■利用している環境

Microsoft SQLServer 2014

■解決方法

1．改行コードの削除

削除したい改行コードは以下となります。
- CR：CHAR(13)
- LF：CHAR(10)
Replace関数を2度利用し、改行コードを削除していきます。

Select  REPLACE(REPLACE(Column1, CHAR(13), ''), CHAR(10), '') From Table1 

2.文字列を特定の区切文字で分割

1. CHARINDEX関数でColumn1の文字列に存在する区切文字を検索して、最初に見つかった位置を取得します。
2. SUBSTRING関数でColumn1の区切文字以前を取得します。
   ※区切文字以降の文字を取得したい場合は最初の区切文字以降を指定することで取得はできます。

Select SUBSTRING( Column1, 1, CHARINDEX( 区切文字 , Column1 ) - 1 ) From Table1 

■試してみる

普通に抽出した場合(before)と関数を使って抽出した場合(after)でテストしてみます。

Select 
	REPLACE
		(REPLACE(
		'ABC
		DEF
		GHI'
		,CHAR(13), '')
	,CHAR(10), '') 

結果

↓今回は縦線「｜」を区切文字として文字列を分割しました。

Select SUBSTRING( 'ABC|DEF', 1, CHARINDEX( '|', 'ABC|DEF' ) - 1 ) 

結果

■補足

文字列区切りに関して、今回は配列として扱うほどではない文字列であったが、
配列として分割したい場合は関数がないので、SQL文を自作して実装が必要になります。
※Microsoft SQLServer 2016以降であれば、STRING_SPLIT という関数が利用可能となっているようです。