STRING_SPLIT (TRANSACT-SQL)STRING_SPLIT (Transact-SQL)

このトピックの対象: ○SQL Server (2016 以降)○Azure SQL Database×Azure SQL Data Warehouse ×Parallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

指定した区切り記号が挿入された文字式を分割します。Splits the character expression using specified separator.

注意

STRING_SPLIT関数は、互換性レベル 130 でのみ使用します。The STRING_SPLIT function is available only under compatibility level 130. データベースの互換性レベルが 130 より下の場合は、SQL Server ことはできませんを見つけて実行するSTRING_SPLIT関数。If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. 次のコマンドを利用し、データベースの互換性レベルを変更できます。You can change a compatibility level of database using the following command:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

互換性レベル 120 は新しい Azure SQL データベースであっても既定でされる可能性がありますに注意してください。Note that compatibility level 120 might be default even in new Azure SQL Databases.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

STRING_SPLIT ( string , separator )  

引数Arguments

stringstring
任意の文字型 (つまりnvarcharvarcharncharまたはchar)。Is an expression of any character type (i.e. nvarchar, varchar, nchar or char).

区切り記号separator
1 つの文字は、任意の文字型 (例: nvarchar(1)varchar (1)nchar (1)またはchar (1)) 連結された文字列の区切り記号として使用されます。Is a single character expression of any character type (e.g. nvarchar(1), varchar(1), nchar(1) or char(1)) that is used as separator for concatenated strings.

戻り値の型Return Types

フラグメントを単一列テーブルを返します。Returns a single-column table with fragments. 列の名前はです。The name of the column is value. 返しますnvarchar場合、入力引数のいずれかのいずれかのnvarcharまたはncharです。Returns nvarchar if any of the input arguments are either nvarchar or nchar. 返しますそれ以外の場合varcharです。Otherwise returns varchar. 戻り値の型の長さは、文字列引数の長さと同じです。The length of the return type is the same as the length of the string argument.

解説Remarks

STRING_SPLITは分割するか、文字列および文字列の分割に使用される区切り記号を取得します。STRING_SPLIT takes a string that should be divided and the separator that will be used to divide string. 部分文字列を含む 1 列のテーブルを返します。It returns a single-column table with substrings. たとえば、次のステートメントSELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');区切り記号として空白文字を使用して、次の結果のテーブルを返します。For example, the following statement SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' '); using the space character as the separator, returns following result table:

valuevalue
LoremLorem
ipsumipsum
dolordolor
座るsit
amet です。amet.

場合は、入力文字列がNULLSTRING_SPLITテーブル値関数は、空のテーブルを返します。If the input string is NULL, the STRING_SPLIT table-valued function returns an empty table.

STRING_SPLIT少なくとも 130 の互換モードです。STRING_SPLIT requires at least compatibility mode 130.

使用例Examples

A.A. 分割のコンマ区切り値の文字列Split comma separated value string

値のコンマ区切り一覧を解析し、すべての空でないトークンを返します。Parse a comma separated list of values and return all non-empty tokens:


DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  

SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';  

区切り記号の間で項目がない場合、STRING_SPLIT は空の文字列を返します。STRING_SPLIT will return empty string if there is nothing between separator. 条件の RTRIM(value) <> ' 空のトークンが削除されます。Condition RTRIM(value) <> '' will remove empty tokens.

B.B. 分割コンマ区切りの列に値の文字列Split comma separated value string in a column

Product テーブルには、次の例に示すようにタグのコンマで区切った一覧の列があります。Product table has a column with comma-separate list of tags shown in the following example:

productIdProductId 名前Name TagsTags
11 フル指 GlovesFull-Finger Gloves clothing、road、touring、自転車clothing,road,touring,bike
22 LL ヘッドセットLL Headset 自転車bike
33 HL Mountain FrameHL Mountain Frame 自転車、mountainbike,mountain

次のクエリでは、各タグのリストを変換し、元の行と結合します。Following query transforms each list of tags and joins them with the original row:

SELECT ProductId, Name, value  
FROM Product  
    CROSS APPLY STRING_SPLIT(Tags, ',');  

以下に結果セットを示します。Here is the result set.

productIdProductId 名前Name valuevalue
11 フル指 GlovesFull-Finger Gloves clothingclothing
11 フル指 GlovesFull-Finger Gloves roadroad
11 フル指 GlovesFull-Finger Gloves touringtouring
11 フル指 GlovesFull-Finger Gloves 自転車bike
22 LL ヘッドセットLL Headset 自転車bike
33 HL Mountain FrameHL Mountain Frame 自転車bike
33 HL Mountain FrameHL Mountain Frame mountainmountain

C.C. 値ごとの集計Aggregation by values

ユーザーは、2 つ以上の製品とタグのみをフィルター処理して、製品の数の順、各タグごとに製品の数を表示するレポートを作成する必要があります。Users must create a report that shows the number of products per each tag, ordered by number of products, and to filter only the tags with more than 2 products.

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.D. タグ値で検索します。Search by tag value

開発者は、キーワードで記事を検索するクエリを作成する必要があります。Developers must create queries that find articles by keywords. これらは、次のクエリを使用できます。They can use following queries:

1 つのタグ (clothing) での製品を検出するには。To find products with a single tag (clothing):

SELECT ProductId, Name, Tags  
FROM Product  
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));  

2 つの指定されたタグ (clothing および道路) で製品を検索するには。Find products with two specified tags (clothing and road):


SELECT ProductId, Name, Tags  
FROM Product  
WHERE EXISTS (SELECT *  
    FROM STRING_SPLIT(Tags, ',')  
    WHERE value IN ('clothing', 'road');  

E.E. 値のリストで行を検索します。Find rows by list of values

開発者は、id の一覧を使用してアーティクルを検索するクエリを作成する必要があります。Developers must create a query that finds articles by a list of ids. これらは、次のクエリを使用できます。They can use following query:

SELECT ProductId, Name, Tags  
FROM Product  
JOIN STRING_SPLIT('1,2,3',',')   
    ON value = ProductId;  

これは、アプリケーション層での動的 SQL 文字列の作成などの共通のアンチ パターンの代わりに、または Transact-SQLTransact-SQL、または LIKE 演算子を使用しています。This is replacement for common anti-pattern such as creating a dynamic SQL string in application layer or Transact-SQLTransact-SQL, or by using LIKE operator:

SELECT ProductId, Name, Tags  
FROM Product  
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';  

参照See Also

文字列関数 (です。TRANSACT-SQL と #41 です。 String Functions (Transact-SQL)
部分文字列と #40 です。TRANSACT-SQL と #41 です。SUBSTRING (Transact-SQL)