How Can I Split Comma-Separated Strings in SQL Server 2012 Without Using STRING_SPLIT?

SQL Server 2012 Comma-Separated String Splitting: An XML-Based Solution

SQL Server 2012's compatibility level can restrict access to the STRING_SPLIT function. This article presents an effective workaround using XML techniques to split comma-separated values.

Leveraging XML and CROSS APPLY

This method transforms the comma-separated string into an XML structure. CROSS APPLY then extracts each value as a node from this XML. Here's the SQL code:

<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>

Illustrative Example

Let's use these sample parameters:

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

The following query demonstrates splitting these strings and inserting the results into a table:

<code class="language-sql">INSERT INTO @StudentsMark
SELECT C.id, C1.marks
FROM CTE C
LEFT JOIN CTE1 C1 ON C1.RN = C.RN;</code>

Outcome

The result is a table with 'id' and 'marks' columns, each populated with the split values from @ID and @Marks.

This XML-based approach offers a reliable method for handling comma-separated data in SQL Server 2012, bypassing compatibility level limitations.

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