STRING_SPLIT (Transact-SQL)

  • [アーティクル]

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric の SQL エンドポイントMicrosoft Fabric のウェアハウス

指定した区切り文字に基づいて、文字列を部分文字列の行に分割するテーブル値関数です。

互換性レベル 130

STRING_SPLIT は、互換性レベル 130 以上にする必要があります。 レベルが 130 未満の場合、SQL Server から STRING_SPLIT 関数を検出できません。

データベースの互換性レベルを変更するには、「データベースの互換性レベルの表示または変更」を参照してください。

Note

Azure Synapse Analytics の STRING_SPLIT に、互換性の構成は必要ありません。

Transact-SQL 構文表記規則

構文

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

引数

string

任意の文字型 (nvarchar、varchar、nchar、char など) の式です。

separator

任意の文字型の 1 文字の式です (nvarchar(1) 、varchar(1) 、nchar(1) 、char(1) など)。連結する部分文字列の区切り文字として使用されます。

enable_ordinal

ordinal 出力列を有効または無効にするフラグとして機能する int または bit式。 値 1 を指定すると、ordinal 列が有効になります。 enable_ordinal が省略されるか NULL の場合、または 0 を指定した場合、ordinal 列は無効になります。

Note

現在、enable_ordinal 引数と ordinal 出力列は、Azure SQL Database、Azure SQL Managed Instance、Azure Synapse Analytics (サーバーレス SQL プールのみ) でサポートされています。 SQL Server 2022 (16.x) 以降、引数と出力列は SQL Server で使用できます。

戻り値の型

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 ステートメントで 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 からは、次の結果テーブルが返されます。

value
Lorem
ipsum
dolor
sit
amet.

次の例では、省略可能な 3 番目の引数に 1 を渡して ordinal 列を有効にします。

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

このステートメントにより、次の結果テーブルが返されます。

value 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 名前 value
1 Full-Finger Gloves clothing
1 Full-Finger Gloves 道路
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;

上のステートメントでは、次のテーブルが返されます。

value ordinal (序数)
テキサス 2
ワシントン 4
コロラド 6

G. 序数値で行を並べ替える

次のステートメントにより、ordinal 列で順序付けされた、入力文字列とその序数値の分割された部分文字列値が返されます。

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

上のステートメントでは、次のテーブルが返されます。

value ordinal (序数)
A 5
B 4
C 3
D 2
E 1

次の手順