SQLServerでテキストまたはBLOBをデータ行とテーブルに分割する
任意の文字で区切られたテキスト文字列リストをデータ行に分割するためのSQLServer関数。他の多くの機能のベースとして使用されます
行へのテキスト
他のSQL関数の記事に続いて、これはCMSドキュメントライブラリを検索するためのコードの一部として使用する関数です。
まず最初に、関数は後で返されるテーブルを宣言します。
次に、重複する区切り文字を取り除き、返されるレコードの量を減らします。
次に、文字列をループして、最初に宣言する区切り文字が出現するたびに、宣言されたテーブルに単語を挿入します。
この関数はスキーマバインディングを使用するため、テーブルにバインドすることもできる他のスキーマバインド関数で使用できます。
SQL Server 2016では、組み込み関数STRING_SPLITが導入され、バージョン130を超える互換性がある限り、単一の区切り文字に使用できます。
作業が進むにつれて、string_splitを超え、特定の列にのみ存在する場合でも、テキスト修飾子を処理できるようになりました。
SQL Code - Basic Function
CREATE FUNCTION [dbo].[TextToRows](@Delim NVARCHAR(10),@Value NVARCHAR(MAX))--Delimeter and Search StringRETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) WITH SCHEMABINDING --Return TableAS BEGINSET @Value=LTRIM(RTRIM(@Value))--Trim forward/trailing spacesWHILE (CHARINDEX(@Delim+@Delim,@Value,1)<>0) BEGINSET @Value=REPLACE(@Value,@Delim+@Delim,@Delim)--Remove double delims (if required)...ENDDECLARE @CurPos BIGINTSET @CurPos=0DECLARE @NextPos BIGINTSET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1)WHILE @NextPos>0 BEGININSERT INTO @Table(WordStr)SELECT REPLACE(SUBSTRING(@Value,@CurPos,(@NextPos-@CurPos)),@Delim,'')--Add first word if existsSET @CurPos=@NextPosSET @NextPos=CHARINDEX(@Delim,@Value,@CurPos+1)ENDINSERT INTO @Table(WordStr) SELECT REPLACE(SUBSTRING(@Value,@CurPos,LEN(@Value)),@Delim,'')--Add last word (or whole word)RETURNENDGOSELECT * FROM TextToRows(',','Gavin,Clayton,Test,Data')Result
Gavin
Clayton
Test
Data
Clayton
Test
Data
New SQL Code - With Text Qualifiers
CREATE FUNCTION dbo.[TextToRowsText](@Delim NVARCHAR(10),@Value NVARCHAR(MAX),@Text NVARCHAR(1))--Delimeter and Search StringRETURNS @Table TABLE(WordInt BIGINT IDENTITY(1,1) PRIMARY KEY,WordStr NVARCHAR(MAX)) --Return TableAS BEGINDECLARE @TextOn INT=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)DECLARE @NextPos BIGINT=CHARINDEX((CASE WHEN @TextOn=1 THEN @Text+@Delim ELSE @Delim END),@Value,LEN(@Delim)+(@TextOn))WHILE @NextPos>0 BEGININSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,LEN(@Text)+@TextOn,(@NextPos-(LEN(@Text)+@TextOn)))SET @Value=SUBSTRING(@Value,@NextPos+@TextOn+LEN(@Delim),9999999)SET @TextOn=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)SET @NextPos=CHARINDEX((CASE WHEN @TextOn=1 THEN @Text+@Delim ELSE @Delim END),@Value,LEN(@Text)+@TextOn)ENDSET @TextOn=(CASE WHEN LEFT(@Value,1)=@Text AND LEN(@Text)>0 THEN 1 ELSE 0 END)IF LEN(@Value)>0 INSERT INTO @Table(WordStr) SELECT SUBSTRING(@Value,LEN(@Text)+@TextOn,(CASE WHEN RIGHT(@Value,1)=@Text THEN LEN(@Value)-(LEN(@Text)+@TextOn) ELSE 9999999 END))RETURNENDGOSELECT * FROM dbo.TextToRowsText(',','Gavin,"Clayton","Test",Data','"')PIVOTで使用
これをそれ自体に適用して複数の区切り文字を分割したり、PIVOTを使用して、結果セットからテーブルを作成したりすることもできます。以下は、テーブルに分割された二重区切り関数です。
これを使用すると、コンピューター間で送信される文字の量をすばやく減らすことができます。必要に応じて、最大10文字の区切り文字を使用できます。
Double Delimited & Pivot
DECLARE @Str NVARCHAR(1000)='1;1.2;1.2.3;1.2.3.4'SELECT * FROM (SELECT ttr.WordStr Orig,ttr2.WordInt,ttr2.WordStrFROM dbo.TextToRows(';',@Str) ttrOUTER APPLY dbo.TextToRows('.',ttr.WordStr) ttr2) ttrdPIVOT (MAX(WordStr) FOR WordInt IN ([1],[2],[3],[4])) PivDouble Delimited Pivot Result
| Orig | 1 | 2 | 3 | 4 |
| 1 | 1 | NULL | NULL | NULL |
| 1.2 | 1 | 2 | NULL | NULL |
| 1.2.3 | 1 | 2 | 3 | NULL |
| 1.2.3.4 | 1 | 2 | 3 | 4 |
