How to Split Comma-Separated Values in SQL Server 2012 without STRING_SPLIT?

Alternatives to STRING_SPLIT in SQL Server 2012

The STRING_SPLIT function is not available in SQL Server 2012 due to compatibility level restrictions. While changing the compatibility level is not possible, there is an alternative to splitting comma-separated values.

Using XML methods and CROSS APPLY

XML methods and CROSS APPLY can be used to achieve the desired results. Here's how it works:

<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

Consider the following example, where the @ID parameter contains comma-separated values:

<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';</code>

The output of the above query will be:

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

Summary

While STRING_SPLIT is not available in SQL Server 2012, the XML method combined with CROSS APPLY provides a viable alternative for splitting comma-separated values. This approach is particularly useful when compatibility level restrictions prevent database modifications.

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