STRING_SPLIT (Transact-SQL)
適用対象: SQL Server 2016 (13.x) 以降
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Microsoft Fabric の SQL 分析エンドポイント
Microsoft Fabric のウェアハウス
STRING_SPLIT
は、指定した区切り文字に基づいて、文字列を部分文字列の行に分割するテーブル値関数です。
互換性レベル 130
STRING_SPLIT
では、互換性レベルを 130 以上にする必要があります。 互換性レベルが 130 未満の場合、データベースエンジンは STRING_SPLIT
関数を見つけることができません。
データベースの互換性レベルを変更するには、「データベースの互換性レベルの表示または変更」を参照してください。
Note
Azure Synapse Analytics の STRING_SPLIT
に、互換性の構成は必要ありません。
構文
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
引数
string
任意の文字型 (nvarchar、varchar、nchar、char など) の式です。
separator
任意の文字型の 1 文字の式です (nvarchar(1) 、varchar(1) 、nchar(1) 、char(1) など)。連結する部分文字列の区切り文字として使用されます。
enable_ordinal
適用対象: Azure SQL Database、Azure SQL Managed Instance、Azure Synapse Analytics (サーバーレス SQL プールのみ)、および SQL Server 2022 (16.x)
ordinal
出力列を有効または無効にするフラグとして機能する int または bit 式。 値 1
を指定すると、ordinal
列が有効になります。 enable_ordinal が省略されるか NULL
の場合、または 0
を指定した場合、ordinal
列は無効になります。
戻り値の型
ordinal
出力列が有効ではない場合、STRING_SPLIT
は行が部分文字列である単一列のテーブルを返します。 列の名前は value
です。 入力引数のいずれかが nvarchar または nchar の場合は、nvarchar が返されます。 それ以外の場合、varchar が返されます。 戻り値の型の長さは、"文字列" 引数の長さと同じです。
enable_ordinal 引数に値 1
が渡された場合、入力文字列内の各部分文字列の位置の 1 から始まるインデックスの値から成る ordinal
という名前の 2 番目の列が返されます。 戻り値の型は bigint です。
解説
STRING_SPLIT
には、部分文字列を区切った文字列と、区切り記号や区切りとして使用される 1 文字を入力します。 関数ではオプションとして、ordinal
出力列をそれぞれ無効または有効にする 0
または 1
の値を持つ 3 番目の引数がサポートされています。
STRING_SPLIT
では、enable_ordinal 引数に応じて、1 列または 2 列のテーブルが出力されます。
enable_ordinal が、
NULL
または省略した場合、または値が0
である場合、STRING_SPLIT
により、行に部分文字列を含む、単一列テーブルが返されます。 出力列の名前はvalue
です。enable_ordinal に値
1
が指定されると、元の入力文字列内で部分文字列の 1 から始まるインデックスの値から成るordinal
列を含む 2 列のテーブルが返されます。
enable_ordinal 引数は定数値である必要があり、列または変数であってはなりません。 また、値が 0
または 1
の bit または int データ型である必要があります。 それ以外の場合、関数ではエラーが発生します。
出力行には任意の順序を指定できます。 この順序が入力文字列の部分文字列の順序と一致するかどうかは保証されません。 SELECT
SELECT ステートメントで ORDER BY
句を使用して、最終的な並べ替え順序をオーバーライドできます (例: ORDER BY value
、ORDER BY ordinal
など)。
0x0000
(char(0)) は Windows 照合順序での未定義の文字で、STRING_SPLIT
に含めることはできません。
入力文字列に区切り文字が 2 つ以上連続して含まれている場合、長さ 0 の空の部分文字列が存在します。 空の部分文字列は、プレーンな部分文字列と同じように扱われます。 WHERE
句 (例: WHERE value <> ''
) を使用して、空の部分文字列が含まれているすべての行をフィルター処理できます。 入力文字列が NULL
の場合、STRING_SPLIT
テーブル値関数により空のテーブルが返されます。
たとえば、次の SELECT
ステートメントでは、空白文字が区切り記号として使用されます。
SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
実際に実行すると、前の SELECT
からは、次の結果テーブルが返されます。
値 |
---|
Lorem |
ipsum |
dolor |
sit |
amet. |
次の例では、省略可能な 3 番目の引数に 1
を渡して ordinal
列を有効にします。
SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);
このステートメントにより、次の結果テーブルが返されます。
値 | ordinal (序数) |
---|---|
Lorem |
1 |
ipsum |
2 |
dolor |
3 |
sit |
4 |
amet. |
5 |
例
A. コンマ区切り値文字列を分割する
コンマ区切り値リストを解析し、空ではないすべてのトークンを返します。
DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'
SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
区切り記号の間に何もない場合、STRING_SPLIT
は空の文字列を返します。 この条件 RTRIM(value) <> ''
により、空のトークンが削除されます。
B. 列内のコンマ区切り値文字列を分割する
次の例のように、Product テーブルには、コンマで区切られたタグのリストを含む列があります。
ProductId | 名前 | タグ |
---|---|---|
1 |
Full-Finger Gloves |
clothing,road,touring,bike |
2 |
LL Headset |
bike |
3 |
HL Mountain Frame |
bike,mountain |
次のクエリは、タグの各リストを変換し、元の行と結合します。
SELECT ProductId, Name, value
FROM Product
CROSS APPLY STRING_SPLIT(Tags, ',');
結果セットは次のようになります。
ProductId | 名前 | 値 |
---|---|---|
1 |
Full-Finger Gloves |
clothing |
1 |
Full-Finger Gloves |
road |
1 |
Full-Finger Gloves |
touring |
1 |
Full-Finger Gloves |
bike |
2 |
LL Headset |
bike |
3 |
HL Mountain Frame |
bike |
3 |
HL Mountain Frame |
mountain |
Note
この順序が入力文字列の部分文字列の順序と一致するかどうかは保証されないため、出力の順序は異なる場合があります。
C: 値による集計
ユーザーは、製品数で並べ替え、タグごとに製品数を表示するレポートを作成し、2 製品を超えるタグのみに絞り込む必要があります。
SELECT value as tag, COUNT(*) AS [number_of_articles]
FROM Product
CROSS APPLY STRING_SPLIT(Tags, ',')
GROUP BY value
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;
D. タグ値で検索する
開発者はキーワードで記事を検索するクエリを作成する必要があります。 この場合、次のクエリを使用できます。
1 つのタグ (clothing) を持つ製品を検索するには:
SELECT ProductId, Name, Tags
FROM Product
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));
指定された 2 つのタグ (clothing と road) を持つ製品を検索するには:
SELECT ProductId, Name, Tags
FROM Product
WHERE EXISTS (SELECT *
FROM STRING_SPLIT(Tags, ',')
WHERE value IN ('clothing', 'road'));
E. 値のリストで行を検索する
開発者は、ID のリストで記事を検索するクエリを作成する必要があります。 この場合、次のクエリを使用できます。
SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
ON value = ProductId;
前の STRING_SPLIT
の使用方法は、一般的なアンチ パターンの置き換えです。 このようなアンチ パターンには、アプリケーション レイヤーまたは Transact-SQL での動的 SQL 文字列の作成が含まれる場合があります。 または、アンチ パターンは、LIKE
演算子を使用して実現できます。 次の例の SELECT
ステートメントを参照してください。
SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';
F. 序数値で行を検索する
次のステートメントを使うと、偶数のインデックス値を持つすべての行が検索されます。
SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;
上のステートメントでは、次のテーブルが返されます。
値 | ordinal (序数) |
---|---|
テキサス | 2 |
ワシントン | 4 |
コロラド | 6 |
G. 序数値で行を並べ替える
次のステートメントにより、ordinal
列で順序付けされた、入力文字列とその序数値の分割された部分文字列値が返されます。
SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;
上のステートメントでは、次のテーブルが返されます。
値 | ordinal (序数) |
---|---|
A | 5 |
B | 4 |
C | 3 |
D | 2 |
E | 1 |