SQLShack
データベース開発者は、カンマで区切られた値やその他の区切り記号の項目を表形式に変換する必要があることがよくあります。 区切り文字には、パイプ「｜」、ハッシュ「#」、ドル「$」などがあります。 SQL Server の互換性レベル 130 およびそれ以降のバージョンでは、デリミタで区切られた値を行（表形式）に変換する string_split() 関数がサポートされています。
なぜプロシージャや T-SQL クエリにカンマ区切りの入力が必要なのか
ループを使ってプログラムを繰り返し実行するのは、データベースだけでなくバックエンド アプリケーションでもよく使われる方法です。 反復回数が少ない場合は実行時間が短くて済みますが、反復回数が多い場合は処理時間が長くなります。 例えば、アプリケーションがデータベースからデータを取得し、異なる入力パラメータを解析してプログラムの一部をループで実行している場合、アプリケーションは各反復のデータベース応答をラップする必要があります。 このような状況に対処するには、同じプログラムを1回だけ実行して、データベースの入力パラメータとしてカンマまたはデリミタで区切られた値を解析し、それをT-SQLプログラムで表形式にして、さらにロジックを進めることができます。
現在、多くのプラットフォームがマイクロサービスアーキテクチャに変更されています。 アプリケーションだけでなく、データベースの設計もマイクロサービスベースのアーキテクチャで構成することができ、データベース間の内部通信のほとんどが整数参照のみで行われています。
同じパラメータへの複数の入力は、ストアド プロシージャの入力パラメータまたは表計算関数への入力でカンマで区切った値を使用し、T-SQL ステートメントで表を使用することで実現できます。 T-SQLを使っていると、この戦略をとる場面が増えるかもしれません。 SQL Serverを使ったプログラミングでは、セパレータを使って文字列を要素に分割する必要があります。 文字列に対してsplit関数を使用すると、セパレータを入力パラメータとして定義します。 文字列全体が分割され、テーブルとして返されます。 特定の状況下では、入力パラメータは、入力セットごとに文字で区切られた2つ以上の値の組み合わせにすることができます。 たとえば、以下のようになります。
N’203616, 198667, 193718, 188769,…’ N’1021|203616$1021|198667$1022|193718$1022|188769$…’ N’1021|203616,198667$1022|193718,188769$…’
カンマ区切りの値を使ったシングルスレッド実行には、SQL Serverのマルチスレッド実行よりも優れた点があります。
- プログラムの一部を単一で実行するため、入力数が少なくて済む
- ループを使用して何度も繰り返すのではなく、データベースのテーブルに対して単一の読み取りまたは書き込み操作を行う
- サーバー資産の使用が 1 回だけで済む
- 複数の結果セットからの応答を設定するためのバックエンド アプリケーションのハードルがなくなる
- ワンショット コミットとは、データベース/SQL Server がトランザクションをブロックして事態を遅らせることがないことを意味します。 したがって、ブロックされる可能性はありません
- トランザクションの一貫性を簡単に監督することができます
ループを使用した分割関数
ほとんどの場合、開発者はカンマで区切られた値からリストやテーブル形式にデータを抽出するために、ループを使用してテーブル値関数を記述します。 ループは、再帰を用いてタプルを 1 つずつ抽出します。 大きな入力パラメータを扱う場合には、抽出に時間がかかるため、パフォーマンスの問題が発生します。 ここでは、split_stringという名前のシンプルな関数を紹介します。この関数は、表形式の関数の中でループを使用しています。 この関数は、2つの引数または入力パラメータ（1 – 入力文字列と2 – 区切り文字）を使用できます。
|
div div div
1
2
3
4
5
6
7
8
9div
10
11
12
13
14
15
16
17
|
CREATE FUNCTION split_string
(
@in_string VARCHAR(MAX),
@delimeter VARCHAR(1)
）
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
WHILE LEN(@in_string) > 0
BEGIN
INSERT INTO @list(tuple)
SELECT left(@in_string, charindex(@delimiter, @in_string+’,’) -1) as tuple
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), ”)
end
RETURN
END
|
この関数が返す結果セットのテーブルには、デリミターで区切られた入力パラメータ文字列のすべての部分文字列が含まれています。 テーブルの中の部分文字列は、入力パラメータの中で起こった順に並んでいます。 区切り文字が入力文字列のどの部分にも対応していない場合、出力される表の構成要素は1つだけとなります。 tabular関数は、カンマで区切られた文字列から行-列形式の結果セットを返します。
|
1
|
SELECT * FROM split_string(‘1001,1002,1003,1004’, ‘,’)
|
最近のバージョンのSQL Serverでは、入力文字列とデリミタを入力パラメータとして同じ作業を行う組み込み関数string_split()が用意されています。
XMLを使用したSplit関数
XMLロジックを使用したSplit関数は、区切り文字列から値を分離するのに便利な方法です。 XML ロジックを T-SQL ベースの XQUERY アプローチで使用すると、T-SQL がループやカーソルを使用した再帰として特徴付けられないことになります。 基本的に、入力パラメータ文字列は、T-SQLアーティキュレーションのXMLノードを置き換えることで、XMLに変換されます。 結果のXMLはXQUERYで使用され、ノード値をテーブル形式に抽出します。 また、パフォーマンスの向上にもつながります。
例えば、以下のT-SQL文では、@user_idsという変数でコンマで区切られた値を持っています。 変数では、カンマをXMLタグに置き換えてXMLデータ型のドキュメントとし、XPATHを「/root/U」としてXQUERYを使って抽出します。
|
1
です。 2
3
4div
5
6
7
8
9
10
|
DECLARE @user_ids NVARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871, 173922, 168973, 164024, 159075, 154126, 149177, 144228, 139279, 134330, 129381, 124432, 119483, 114534, 109585, 104636, 99687, 94738, 89789, 84840, 79891, 74942, 69993, 65044, 60095, 55146′
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U></U></U></root>’ AS XML）
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
INTO #users
FROM @sql_xml.nodes(‘/root/U’) f(x)
SELECT *
FROM #users
|
上記の関数では、VARCHAR (
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
, ‘</U><U></U></root>’ AS XML)
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020