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 and above. データベースの互換性レベルが 130 よりも低い場合、 SQL ServerSQL ServerSTRING_SPLIT 関数を見つけて実行することができません。If your database compatibility level is lower than 130, SQL ServerSQL Server will not be able to find and execute STRING_SPLIT function. データベースの互換性レベルを変更するには、「データベースの互換性レベルの表示または変更」を参照してください。To change the compatibility level of a database, refer to View or Change the Compatibility Level of a Database. 新しい Azure SQL データベースAzure SQL Database であっても、互換性レベル 120 が既定値の場合があります。Note that compatibility level 120 might be default even in new Azure SQL データベースAzure SQL Database.

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

構文Syntax

STRING_SPLIT ( string , separator )  

引数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 strings.

戻り値の型Return Types

フラグメントを含む単一列のテーブルを返します。Returns a single-column table with fragments. 列の名前は 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.

RemarksRemarks

STRING_SPLIT は、分割される文字列と、文字列の分割に使用される区切り記号を受け取ります。STRING_SPLIT takes a string that should be divided and the separator that will be used to divide string. サブ文字列を含む単一列のテーブルを返します。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
sitsit
amet.amet.

入力文字列が NULL の場合、STRING_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
@shouldalert1 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
@shouldalert1 Full-Finger GlovesFull-Finger Gloves clothingclothing
@shouldalert1 Full-Finger GlovesFull-Finger Gloves roadroad
@shouldalert1 Full-Finger GlovesFull-Finger Gloves touringtouring
@shouldalert1 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

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;  

これは、アプリケーション レイヤーまたは Transact-SQLTransact-SQL で動的な 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

LEFT (Transact-SQL) LEFT (Transact-SQL)
LTRIM (Transact-SQL) LTRIM (Transact-SQL)
RIGHT (Transact-SQL) RIGHT (Transact-SQL)
RTRIM (Transact-SQL) RTRIM (Transact-SQL)
SUBSTRING (Transact-SQL) SUBSTRING (Transact-SQL)
TRIM (Transact-SQL) TRIM (Transact-SQL)
文字列関数 (Transact-SQL)String Functions (Transact-SQL)