split – STRING_SPLIT in SQL Server 2012
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;
Inline function based on Yogesh Sharma and Salman A answers:
Create FUNCTION [dbo].[fn_split_string]
(
@string nvarchar(max),
@delimiter nvarchar(max)
)
/*
The same as STRING_SPLIT for compatibility level < 130
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
*/
RETURNS TABLE AS RETURN
(
SELECT
--ROW_NUMBER ( ) over(order by (select 0)) AS id -- intuitive, but not correect
Split.a.value(let $n := . return count(../*[. << $n]) + 1, int) AS id
, Split.a.value(., NVARCHAR(MAX)) AS value
FROM
(
SELECT CAST(<X>+REPLACE(@string, @delimiter, </X><X>)+</X> AS XML) AS String
) AS a
CROSS APPLY String.nodes(/X) AS Split(a)
)
Example:
DECLARE @ID NVARCHAR(300)= abc,d,e,f,g;
select * from fn_split_string(@ID,,)
-- If you need exactly string_split functionality (without id column):
select value from fn_split_string(@ID,,)
split – STRING_SPLIT in SQL Server 2012
Another approach would be to use CHARINDEX and SUBSTRING in a WHILE:
DECLARE @IDs VARCHAR(500);
DECLARE @Number VARCHAR(500);
DECLARE @charSpliter CHAR;
SET @charSpliter = ,;
SET @IDs = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 + @charSpliter;
WHILE CHARINDEX(@charSpliter, @IDs) > 0
BEGIN
SET @Number = SUBSTRING(@IDs, 0, CHARINDEX(@charSpliter, @IDs));
SET @IDs = SUBSTRING(@IDs, CHARINDEX(@charSpliter, @IDs) + 1, LEN(@IDs));
PRINT @Number;
END;
Related posts
