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

適用対象: はいSQL Server 2016 and later はいAzure SQL Database はいAzure Synapse Analytics (SQL DW) いいえParallel Data Warehouse APPLIES TO: YesSQL Server 2016 and later YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

指定した区切り文字に基づいて、文字列を部分文字列の行に分割するテーブル値関数です。A table-valued function that splits a string into rows of substrings, based on a specified separator character.

互換性レベル 130Compatibility level 130

STRING_SPLIT は、互換性レベル 130 以上にする必要があります。STRING_SPLIT requires the compatibility level to be at least 130. レベルが 130 未満の場合、SQL Server から STRING_SPLIT 関数を検出できません。When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

データベースの互換性レベルを変更するには、「データベースの互換性レベルの表示または変更」を参照してください。To change the compatibility level of a database, refer to View or Change the Compatibility Level of a Database.

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

構文Syntax

STRING_SPLIT ( string , separator )  

注意

SQL Server 2014 以前の Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

引数Arguments

stringstring
任意の文字型 (nvarcharvarcharncharchar など) のです。Is an expression of any character type (for example, nvarchar, varchar, nchar, or char).

separatorseparator
任意の文字型の 1 文字のです (nvarchar(1)varchar(1)nchar(1)char(1) など)。連結する部分文字列の区切り文字として使用されます。Is a single character expression of any character type (for example, nvarchar(1), varchar(1), nchar(1), or char(1)) that is used as separator for concatenated substrings.

戻り値の型Return Types

行が部分文字列の、単一列のテーブルを返します。Returns a single-column table whose rows are the substrings. 列の名前は value です。The name of the column is value. 入力引数のいずれかが nvarchar または nchar の場合は、nvarchar を返します。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 には、部分文字列を区切った文字列と、区切り記号や区切りとして使用される 1 文字を入力します。STRING_SPLIT inputs a string that has delimited substrings, and inputs one character to use as the delimiter or separator. STRING_SPLIT では、行に部分文字列が含まれる、単一列テーブルが出力されます。STRING_SPLIT outputs a single-column table whose rows contain the substrings. 出力列の名前は value です。The name of the output column is value.

出力行には任意の順序を指定できます。The output rows might be in any order. この順序が入力文字列の部分文字列の順序と一致するかどうかは保証_されません_。The order is not guaranteed to match the order of the substrings in the input string. SELECT ステートメントで ORDER BY 句 (ORDER BY value) を使用して、最終的な並べ替え順序をオーバーライドできます。You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).

0x0000 (char(0) ) の Windows 照合順序で未定義の文字は、STRING_SPLIT に含めることができません。0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in STRING_SPLIT.

入力文字列に区切り文字が 2 つ以上連続して含まれている場合、長さ 0 の空の部分文字列が存在します。Empty zero-length substrings are present when the input string contains two or more consecutive occurrences of the delimiter character. 空の部分文字列は、プレーンな部分文字列と同じように扱われます。Empty substrings are treated the same as are plain substrings. WHERE 句 (WHERE value <> '') を使用して、空の部分文字列が含まれているすべての行をフィルター処理できます。You can filter out any rows that contain the empty substring by using the WHERE clause (WHERE value <> ''). 入力文字列が NULL の場合、STRING_SPLIT テーブル値関数は空のテーブルを返します。If the input string is NULL, the STRING_SPLIT table-valued function returns an empty table.

たとえば、次の SELECT ステートメントでは、空白文字が区切り記号として使用されます。As an example, the following SELECT statement uses the space character as the separator:

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

実際に実行すると、前の SELECT からは、次の結果テーブルが返されます。In a practice run, the preceding SELECT returned following result table:

valuevalue
LoremLorem
ipsumipsum
dolordolor
sitsit
amet.amet.
 

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 Full-Finger GlovesFull-Finger Gloves clothing,road,touring,bikeclothing,road,touring,bike
22 LL HeadsetLL Headset bikebike
33 HL Mountain FrameHL Mountain Frame bike,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 Full-Finger GlovesFull-Finger Gloves clothingclothing
11 Full-Finger GlovesFull-Finger Gloves 道路road
11 Full-Finger GlovesFull-Finger Gloves touringtouring
11 Full-Finger GlovesFull-Finger Gloves bikebike
22 LL HeadsetLL Headset bikebike
33 HL Mountain FrameHL Mountain Frame bikebike
33 HL Mountain FrameHL Mountain Frame mountainmountain

注意

この順序が入力文字列の部分文字列の順序と一致するかどうかは保証 "されない" ため、出力の順序は異なる場合があります。The order of the output may vary as the order is not guaranteed to match the order of the substrings in the input string.

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 two 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 と road) を持つ製品を検索するには: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;  

前の STRING_SPLIT の使用方法は、一般的なアンチ パターンの置き換えです。The preceding STRING_SPLIT usage is a replacement for a common anti-pattern. このようなアンチ パターンには、アプリケーション レイヤーまたは Transact-SQL での動的 SQL 文字列の作成が含まれる場合があります。Such an anti-pattern can involve the creation of a dynamic SQL string in the application layer or in Transact-SQL. または、アンチ パターンは、LIKE 演算子を使用して実現できます。Or an anti-pattern can be achieved by using the LIKE operator. 次の例の SELECT ステートメントを参照してください。See the following example SELECT statement:

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

参照See Also