How to Split Comma-Delimited Strings into Rows in T-SQL?

String splitting techniques in T-SQL

Splitting a comma-separated string into multiple lines is a common task in SQL. T-SQL (Microsoft's implementation of SQL) provides several ways to achieve this goal.

Use XML

One way is to convert the string to XML. The XML structure allows for easy extraction of individual values ​​as XML nodes:

<code class="language-sql">DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)</code>

Use CTE (Common Table Expression)

An alternative is to use CTE to create a series of lines representing string characters:

<code class="language-sql">DECLARE @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
;WITH cte AS
(
    SELECT 0 a, 1 b
    UNION ALL
    SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
    FROM CTE
    WHERE b > a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b > LEN(@delimiter) 
    THEN b - a - LEN(@delimiter) 
    ELSE LEN(@str) - a + 1 END) value      
FROM cte WHERE a > 0</code>

Other methods

Other ways to split strings in T-SQL include using the CHARINDEX() function and the REGEX_SPLIT_TO_TABLE() function (introduced in SQL Server 2016). For more examples and discussion, see related articles like "How to split a comma-separated string?"

The above is the detailed content of How to Split Comma-Delimited Strings into Rows in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!