Articles

SQLShack

データベース開発者は、カンマで区切られた値やその他の区切り記号の項目を表形式に変換する必要があることがよくあります。 区切り文字には、パイプ「|」、ハッシュ「#」、ドル「$」などがあります。 SQL Server の互換性レベル 130 およびそれ以降のバージョンでは、デリミタで区切られた値を行(表形式)に変換する string_split() 関数がサポートされています。

なぜプロシージャや T-SQL クエリにカンマ区切りの入力が必要なのか

ループを使ってプログラムを繰り返し実行するのは、データベースだけでなくバックエンド アプリケーションでもよく使われる方法です。 反復回数が少ない場合は実行時間が短くて済みますが、反復回数が多い場合は処理時間が長くなります。 例えば、アプリケーションがデータベースからデータを取得し、異なる入力パラメータを解析してプログラムの一部をループで実行している場合、アプリケーションは各反復のデータベース応答をラップする必要があります。 このような状況に対処するには、同じプログラムを1回だけ実行して、データベースの入力パラメータとしてカンマまたはデリミタで区切られた値を解析し、それをT-SQLプログラムで表形式にして、さらにロジックを進めることができます。

SQLスレッドの実行

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

T-?

T-カンマで区切られたリストのSQLコード

上記の関数では、VARCHAR (

)を使用しています。 抽出された値には、BIGINTではなくVARCHAR(100)のデータ型を使用しています。 入力パラメータの文字列にも変更がある可能性があります。 例えば、入力文字列の先頭または末尾に余分な「,」が1つある場合、BIGINTデータ型の場合は「0」、VARCHARの場合は「」と、1行余分に返されます。

例えば、以下のようになります。

1
2divdiv

3
4
5

DECLARE @user_ids VARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871,’
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U></U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
FROM @sql_xml.nodes(‘/root/U’) f(x)

T-SQLサンプル

T-SQLサンプル

このように結果セットに「または0」のデータが含まれている場合に対処するには、以下の条件を追加します。 T-SQL文の中に以下のような条件を追加します。

1
2
3
4
5
6

DECLARE @user_ids VARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871,’
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, ‘</U></U></U></root>’ AS XML)
SELECT f.x.value(‘.’, ‘BIGINT’) AS user_id
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘BIGINT’) <> 0

ここでは、f.x.value(‘.’, ‘BIGINT’) <> 0を除外し、f.x.value(‘.’, ‘VARCHAR(100)’) <> ” を除外して、クエリの結果セットにしています。 このT-SQL条件は、テーブル値関数に入力パラメータの数を追加することで、この余分なデリミタを扱うことができます。

関数です。

1
2
3
4
5
6
7
8
9div

10
11
12
13
14
15
16
17

CREATE FUNCTION split_string_XML
(
@in_string VARCHAR(MAX),
@delimeter VARCHAR(1)
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
DECLARE @sql_xml XML = Cast(‘< root><U>’+ Replace(@in_string, @delimeterです。 ‘</U></U></U></root>’ AS XML)
INSERT INTO @list(tuple)
SELECT f.x.value(‘.’, ‘VARCHAR(100)’) AS tuple
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘BIGINT’) <> 0
RETURN
END

実行してみます。

1
2

SELECT *
FROM split_string_XML(‘203616,198667,193718,188769,183820,178871,173922,’, ‘,’)

区切り文字列の文字の組み合わせ

しかし、入力パラメータが複数の区切り文字を持つ2つの値の組み合わせの場合はどうでしょうか。 この場合、以下のようにサブクエリの助けを借りて入力パラメータを2回抽出する必要があります。

1
2
3
4
5
6
7
8
9
10
11
12

DECLARE @in_string VARCHAR(MAX) = ‘1021|203616$1021|198667$1022|193718$1022|188769 XML = Cast(‘<root><U>’+ Replace(@in_string, ‘ ‘</U></U></U></root>’ AS XML)
SELECT X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS role_id,
X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS user_id
FROM
(
SELECT Cast(‘<root><U>’+ Replace(f.x.value(‘.’, ‘varchar(max)’), ‘|’, ‘</U></U></U></root>’ AS XML)AS xml_
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘VARCHAR(MAX)’) <> ”
)T
OUTER APPLY T.xml_.nodes(‘root’) as X(Y)

この例は上記の例と似ていますが、パイプ「|」をデリミタとして利用し、2つ目のデリミタとしてドル「$」を利用しています。 ここでは、role_idとuser_idという2つの列の組み合わせが入力パラメータとなります。 以下に示すように、user_idとrole_idはテーブルの結果セットの中で別々の列になっています。

T-SQL code for combinational delimiter separated to list

T-SQL code for combinational delimiter separated to list

上記の関数では、文字で区切られた単一の文字列や、文字で区切られた文字列の組み合わせなど、任意の文字を使用できます。 開発者は、上記のT-SQL文の中でその文字を単純に置き換える必要があります。 ここで説明した手順で、あらゆる区切り文字列をリストに変換することができます。 SQL ServerのXML関数は文字列の入力を受け付け、入力された文字列は表形式の関数を使って指定されたセパレータで区切られることを覚えておいてください。

  • 著者
  • 最近の投稿
Jignesh Raiyani
Jigneshは、データベースソリューションとアーキテクチャの分野で豊富な経験を持っています。

ジグネッシュ氏は、データベースソリューションとアーキテクチャの分野で豊富な経験を持っており、複数の顧客に対して、データベース設計、SQL開発、管理、クエリの最適化、パフォーマンスチューニング、HAとディザスタリカバリを提供しています。
Jignesh Raiyaniのすべての投稿を見る

Jignesh Raiyani
Jignesh Raiyaniの最新の投稿 (すべて見る)
  • SQL ServerのPage Life Expectancy (PLE) – July 17, 2020年
  • SQL Serverでテーブルパーティショニングを自動化する方法 – 2020年7月7日
  • AWS EC2上でSQL Server Always On Availability Groupsを構成する – 2020年7月6日

DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@in_string, ‘$’, ‘</U><U></U></root>’ AS XML)
SELECT X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS role_id,
X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS user_id
FROM
(
SELECT Cast(‘<root><U>’+ Replace(f.x.value(‘.’, ‘VARCHAR(MAX)’), ‘|’, ‘</U><U></U></root>’ AS XML) AS xml_
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘VARCHAR(MAX)’) <> ”
)T
OUTER APPLY T.xml_.nodes(‘root’) as X(Y)

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:

T-SQL code for combinational delimiter separated to list

T-SQL code for combinational delimiter separated to list

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
Jignesh Raiyani
Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.
View all posts by Jignesh Raiyani

Jignesh Raiyani
Latest posts by Jignesh Raiyani (see all)
  • 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)

SELECT X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS role_id,
X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS user_id
FROM
(
SELECT Cast(‘<root><U>’+ Replace(f.x.value(‘.’, ‘VARCHAR(MAX)’), ‘|’, ‘</U><U></U></root>’ AS XML) AS xml_
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘VARCHAR(MAX)’) <> ”
)T
OUTER APPLY T.xml_.nodes(‘root’) as X(Y)

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:

T-SQL code for combinational delimiter separated to list

T-SQL code for combinational delimiter separated to list

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
Jignesh Raiyani
Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.
View all posts by Jignesh Raiyani

Jignesh Raiyani
Latest posts by Jignesh Raiyani (see all)
  • 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

DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@in_string, ‘$’, ‘</U><U></U></root>’ AS XML)
SELECT X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS role_id,
X.Y.value(‘(U)’, ‘VARCHAR(20)’) AS user_id
FROM
(
SELECT Cast(‘<root><U>’+ Replace(f.x.value(‘.’, ‘VARCHAR(MAX)’), ‘|’, ‘</U><U></U></root>’ AS XML) AS xml_
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘VARCHAR(MAX)’) <> ”
)T
OUTER APPLY T.xml_.nodes(‘root’) as X(Y)

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:

T-SQL code for combinational delimiter separated to list

T-SQL code for combinational delimiter separated to list

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
Jignesh Raiyani
Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.
View all posts by Jignesh Raiyani

Jignesh Raiyani
Latest posts by Jignesh Raiyani (see all)
  • 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

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です