STRING_SPLIT in SQL Server 2012

Question

I have this parameter

@ID varchar = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20'  

I want to do something to split the comma-separated values.

The string_split function doesn't work and I get this error:

The STRING_SPLIT function is available only under compatibility level 130

and I try to alter my database and set the compatibility to 130 but I don't have a permission for this change.

Answer 1

Other approach is too use XML Method with CROSS APPLY to split your Comma Separated Data :

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

Result :

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

Example :

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) );  --insert into @StudentsMark  ;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;