How to Split Comma-Separated Strings in SQL Server 2012?

Replacement of STRING_SPLIT function in SQL Server 2012

The STRING_SPLIT function introduced in SQL Server 2016 provides a convenient way to split a comma-delimited string into individual elements. However, for those using SQL Server 2012, a workaround is required due to the lack of this functionality.

Alternative Splitting Methods

To split comma-separated values ​​in SQL Server 2012, consider using the following XML-based method with CROSS APPLY:

<code class="language-sql">SELECT Split.a.value('.', 'NVARCHAR(MAX)') AS DATA
FROM
(
    SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a);</code>

Example usage

Assume the parameter @ID contains a comma-separated list of numbers:

<code class="language-sql">@ID varchar = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';</code>

Execute a query to split the @ID into its component parts:

<code>DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20</code>

Advanced Examples

This method can be extended to create more complex queries. For example, the following query splits @IDs and @Marks (another comma-separated list) into a table called @StudentsMark and associates each ID with its corresponding score:

<code class="language-sql">DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id    NVARCHAR(300),
 marks NVARCHAR(300)
); 
;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a))
     INSERT INTO @StudentsMark
            SELECT C.id,
                   C1.marks
            FROM CTE C
                 LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
SELECT *
FROM @StudentsMark;</code>

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