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

適用於: 是SQL Server 2016 與更新版本 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 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 )  

引數Arguments

stringstring
這是任何字元類型 (例如 nvarcharvarcharncharchar) 的運算式Is an expression of any character type (for example, nvarchar, varchar, nchar, or char).

separatorseparator
這是任何字元類型 (例如 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. 資料行的名稱是 valueThe name of the column is value. 如果任何輸入引數是 nvarcharnchar,則傳回 nvarcharReturns nvarchar if any of the input arguments are either nvarchar or nchar. 否則傳回 varcharOtherwise returns varchar. 傳回類型的長度與字串引數的長度相同。The length of the return type is the same as the length of the string argument.

備註Remarks

STRING_SPLIT 輸入有已分隔之子字串的字串,並輸入一個字元作為分隔符號 (delimiter) 或分隔符號 (separator)。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. 輸出資料行的名稱為 valueThe 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).

當輸入字串包含兩個或更多個連續出現的分隔符號字元時,會出現長度為零的空白子字串。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

使用者必須建立顯示每個標籤之產品數的報告,依據產品數排序,並僅篩選超過兩個產品的標籤。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:

若要尋找有單一標籤 (clothing) 的產品:To find products with a single tag (clothing):

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

尋找有兩個指定標籤 (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

開發人員必須建立依據識別碼清單尋找發行項的查詢。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

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)