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.
Other approach is to 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://learn.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,',')
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;
Alternate function to String_Split for older versions of SQL Server
Create this Function in your Database
Create Function dbo.Test_Split( @string varchar(4000))
Returns
@Result Table (value varchar(100))
As
Begin
declare @len int, @loc int = 1
While @loc <= len(@string)
Begin
Set @len = CHARINDEX(',', @string, @loc) - @loc
If @Len < 0 Set @Len = len(@string)
Insert Into @Result Values (SUBSTRING(@string,@loc,@len))
Set @loc = @loc + @len + 1
End
Return
End
How to Use
Select
*
From
dbo.Test_Split('First,Second,Third,Fourth')
For those looking how to transform multi-line text into rows, here is a code based on the answer:
declare @text varchar(max) = 'line0
line1
line2'
select split.a.value('.', 'nvarchar(max)') data
from
(
select cast('<x>' + replace(@text, char(13) + char(10), '</x><x>') + '</x>' as xml) as string
) as a
cross apply string.nodes('/x') as split(a)
Thank you al3x-m. i create this function for reused in many time.
CREATE FUNCTION STRING_SPLIT_POLYFILL2016 (
@string NVARCHAR(4000)
,@separator NVARCHAR(4000)
)
RETURNS @T TABLE (ColName VARCHAR(4000))
AS
BEGIN
/*
pitt phunsanit
polyfill of STRING_SPLIT in SQL Server 2016
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012
*/
DECLARE @Number VARCHAR(4000);
SET @string = @string + @separator;
WHILE CHARINDEX(@separator, @string) > 0
BEGIN
SET @Number = SUBSTRING(@string, 0, CHARINDEX(@separator, @string));
SET @string = SUBSTRING(@string, CHARINDEX(@separator, @string) + 1, LEN(@string));
INSERT INTO @T (ColName)
VALUES (@Number)
END
RETURN
END
-- DEMO
DECLARE @IDS NVARCHAR(4000)
SET @IDS = 'pitt,phunsanit,01,02,03,4,5,6,7,8,9,10,พิชญ์,พันธุ์สนิท'
SELECT *
FROM STRING_SPLIT_POLYFILL2016(@IDS, ',')
A little variation of @Al3x_M's polyfill, when it is not possible to change the database compatibility level : I use a TABLE variable to store the list of value, for using them later in another query:
DECLARE @IDs VARCHAR(500);
SET @IDs = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2a0' ;
declare @list TABLE (id int);
DECLARE @Number int, @idx int
DECLARE @charSpliter CHAR;
SET @charSpliter = ','
SET @IDs = @IDs + @charSpliter;
set @idx = 0
WHILE (1 = 1)
BEGIN
set @idx = CHARINDEX(@charSpliter, @IDs)
if (@idx is NULL or @idx <= 0) break;
BEGIN TRY
SET @Number = SUBSTRING(@IDs, 0, @idx)
SET @IDs = SUBSTRING(@IDs, @idx + 1, LEN(@IDs))
insert @list select convert(int, @Number)
END TRY
BEGIN CATCH
break
END CATCH
END
-- @list available for the next query...
select * from @list
Add a another method to split string. It is faster I used. especially to split log string. more information please refer: https://sqlperformance.com/2012/07/t-sql-queries/split-strings
create FUNCTION [dbo].[UFN_STRING_SPLIT]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(max)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a
CROSS APPLY x.nodes('i') AS y(i)
);
test code:
declare @sql nvarchar(max)
set @sql ='ZJNB015,ZJNB014,ZJNB008,ZJNB005,ZJJX018,ZJJX013,ZJJX011,ZJJX007,ZJHZ092,ZJHZ090,ZJHZ088,ZJHZ086,ZJHZ066,ZJHZ063,ZJHZ061,ZJHZ058,ZJHZ047,ZJHZ009,YNKM156,YNKM155,YNKM153,YNKM152,YNKM151,YNKM150,YNKM148,YNKM147,YNKM146,YNKM144,YNKM143,YNKM142,YNKM141,YNKM133,YNKM132,YNKM130,YNKM128,YNKM127,YNKM125,YNKM124,YNKM098,YNKM097,YNKM093,YNKM092,YNKM085,YNKM079,YNKM059,YNKM057,YNKM025,YNKM019,YNKM017,YNKM015,YNKM013,YNKM012,YNKM011,YNKM009,YNKM008,YNKM007,YNKM006,YNKM005,XJWLMQ047,XJWLMQ038,XJWLMQ022,XJWLMQ014,TJTJ129,TJTJ104,TJTJ090,TJTJ089,TJTJ085,TJTJ084,TJTJ065,TJTJ061,TJTJ058,TJTJ055,TJTJ050,TJTJ038,TJTJ036,TJTJ026,TJTJ024,TJTJ022,TJTJ021,TJTJ019,TJTJ018,TJTJ015,TJTJ009,TJTJ008,TJTJ003,SXYC001,SXXA037,SXXA027,SXXA021,SXXA020,SXXA013,SXXA012,SXXA011,SXXA005,SXXA002,SXTY044,SXTY039,SXTY032,SXTY024,SXTY022,SXTY012,SXTY009,SXTY008,SXDT001,SX006222,SX006183,SX006176,SX006152,SX005976,SX005937,SX005799,SX005668,SX005407,SX000825,SX000403,SX000194,SX000171,SX000130,SX000081,SX000078,SX000045,SX000036,SX000018,SX000003,SNC001,SHSH167,SHSH165,SHSH164,SHSH163,SHSH162,SHSH161,SHSH158,SHSH157,SHSH155,SHSH154,SHSH153,SHSH148,SHSH147,SHSH144,SHSH137,SHSH132,SHSH125,SHSH123,SHSH122,SHSH119,SHSH118,SHSH112,SHSH106,SHSH095,SHSH094,SHSH092,SHSH075,SHSH068,SHSH051,SHSH034,SHSH029,SHSH023,SHSH014,SHSH011,SHSH010,SHSH008,SHSH006,SHSH005,SHSH004,SDWZB006,SDWTA002,SDWTA001,SDWJN022,SDWH003,SDQD020,SDQD008,SDJN016,SDJN008,SDCD064,SDCD062,SDCD059,SCYB007,SCNJ010,SCNC022,SCMY029,SCMY028,SCMY014,SCLZ019,SCLS016,SCGY010,SCDY011,SCDY007,SCCD200,SCCD199,SCCD198,SCCD197,SCCD196,SCCD195,SCCD194,SCCD193,SCCD192,SCCD191,SCCD188,SCCD187,SCCD186,SCCD185,SCCD183,SCCD182,SCCD181,SCCD179,SCCD176,SCCD174,SCCD173,SCCD172,SCCD171,SCCD166,SCCD165,SCCD164,SCCD163,SCCD162,SCCD161,SCCD160,SCCD158,SCCD157,SCCD152,SCCD150,SCCD144,SCCD136,SCCD123,SCCD117,SCCD110,SCCD102,SCCD080,SCCD053,SCCD051,SCCD040,SCCD031,SCCD025,SCCD020,SCCD019,SCCD017,SCCD013,SCCD004,SCCD003,SCCD002,QHXN006,QHXN005,QHXN004,NMHHHT003,NMHHHT002,NMBT001,LNSY034,LNSY017,LNSY010,LNSY006,LNSY002,LNSY001,LNJZ008,LNDL030,LNDL016,LNDL008,LNDL005,LNDL002,LNAS008,LNAS004,JXYC109,JXYC065,JXNC1001,JXNC098,JXNC082,JXNC073,JXNC069,JXNC067,JXNC066,JXNC065,JXNC064,JXNC063,JXNC062,JXNC045,JXNC026,JXNC003,JXJJ063,JXJJ061,JXJJ012,JXGZ110,JXGZ106,JXGZ1006,JXGZ1004,JXGZ085,JXGZ068,JXGZ031,JXGZ015,JXGZ011,JXGZ007,JXFZ101,JSZJ010,JSZJ002,JSYZ082,JSYZ081,JSYZ080,JSYZ076,JSYZ075,JSYZ074,JSYZ069,JSYZ067,JSXZ071,JSXZ070,JSXZ068,JSXZ067,JSXZ066,JSXZ064,JSXZ063,JSXZ058,JSXZ057,JSXZ053,JSXZ052,JSXZ051,JSXZ050,JSXZ049,JSXZ046,JSXZ043,JSXZ025,JSXZ022,JSXZ020,JSXZ012,JSXZ011,JSXZ007,JSWX057,JSWX056,JSWX055,JSWX053,JSWX052,JSWX051,JSWX050,JSWX047,JSWX045,JSWX042,JSWX041,JSWX038,JSWX032,JSWX030,JSWX029,JSWX016,JSWX014,JSSZ062,JSSZ061,JSSZ056,JSSZ054,JSSZ052,JSSZ049,JSSZ034,JSSZ031,JSSZ030,JSSZ026,JSSZ023,JSSZ020,JSSZ004,JSSZ002,JSSQ022,JSNT014,JSNT013,JSNJ32,JSNJ093,JSNJ092,JSNJ089,JSNJ087,JSNJ086,JSNJ085,JSNJ084,JSNJ081,JSNJ079,JSNJ078,JSNJ075,JSNJ074,JSNJ071,JSNJ070,JSNJ069,JSNJ066,JSNJ065,JSNJ064,JSNJ063,JSNJ062,JSNJ061,JSNJ052,JSNJ042,JSNJ028,JSNJ026,JSNJ021,JSNJ017,JSNJ015,JSNJ013,JSNJ009,JSLYG104,JSLYG001,JSKS003,JSKS002,JSHA027,JSHA026,JSHA025,JSHA021,JNDZ2,JLJL001,JLCC045,JLCC033,JLCC024,JLCC016,JLCC010,JLCC003,IN995904,IN994165,IN994017,IN976180,IN974591,IN0510360,IN018611,IN017781,IN0176312,IN0174673,IN016272,IN0157453,IN0153814,IN0152570,IN0139922,IN0139920,IN0136951,IN0136160,IN012803,IN0126336,IN0123703,IN011648,IN0115515,IN0099299,IN0092308,IN007290,IN007253,IN006969,IN0066740,IN005618,IN005452,IN005309,IN0051963,IN005110,IN005109,IN005106,IN0050678,IN004129,IN004113,IN004103,IN0032362,IN0028960,IN0028115,IN0023061,IN002245,IN002214,IN0021930,IN002072,IN002035,IN001925,IN001827,IN0017119,IN001708,IN001620,IN001613,IN001611,IN001598,IN001577,IN001520,IN001454,IN001391,IN001374,IN001371,IN001367,IN001365,IN001360,IN001357,IN001345,IN001340,IN001335,IN001333,IN001300,IN001255,IN001248,IN001247,IN001240,IN001225,IN001211,IN001183,IN001181,IN001175,IN001173,IN001144,IN001096,IN001085,IN001073,IN0010637,IN001008,IN000997,IN000995,IN000984,IN000963,IN000958,IN000955,IN000930,IN000925,IN000885,IN000856,IN000813,IN000811,IN000772,IN000408,IN000334,IN000332,IN000325,IN000314,IN000267,IN000217,IN000148,IN000147,IN000145,IN000144,IN000126,IN000118,IN000117,IN000115,IN000114,IN000111,IN000107,IN000092,IN000086,IN000081,IN000073,IN000057,IN000051,IN000029,IN000026,IN000021,IN000018,IN000013,IN000006,HUNZZ077,HUNZZ062,HUNZZ003,HUNYY058,HUNYY002,HUNXT063,HUNSY072,HUNHY076,HUNHY061,HUNHY049,HUNHY008,HUNHY002,HUNHY001,HUNHH080,HUNCS090,HUNCS086,HUNCS085,HUNCS084,HUNCS082,HUNCS081,HUNCS079,HUNCS078,HUNCS076,HUNCS074,HUNCS069,HUNCS061,HUNCS051,HUNCS050,HUNCS046,HUNCS044,HUNCS043,HUNCS037,HUNCS032,HUNCS031,HUNCS012,HUNCS003,HUNCD059,HNZZ1004,HNZZ070,HNZZ064,HNZZ061,HNZZ060,HNZZ056,HNZZ055,HNZZ054,HNZZ052,HNZZ051,HNZZ049,HNZZ037,HNZZ035,HNZZ012,HNZZ011,HNZZ009,HNZZ008,HNZZ004,HNZZ003,HNXX065,HNXX008,HNXX002,HNNY086,HNNY085,HNNY084,HNNY080,HNLY1008,HNLY1007,HNLY071,HNLY068,HNLY050,HNLH075,HNLH041,HNKF1005,HNKF005,HNKF004,HNAY082,HLJHRB033,HLJHRB030,HLJHRB018,HLJHRB012,HLJHRB006,HBYC105,HBYC010,HBYC008,HBYC001,HBXY027,HBXY026,HBXY025,HBXY022,HBXY018,HBXY015,HBXY009,HBXN010,HBXN009,HBXN005,HBXG021,HBXG020,HBXG019,HBXG016,HBXG007,HBWH219,HBWH218,HBWH217,HBWH214,HBWH213,HBWH212,HBWH207,HBWH206,HBWH205,HBWH203,HBWH202,HBWH193,HBWH190,HBWH188,HBWH187,HBWH183,HBWH181,HBWH176,HBWH173,HBWH168,HBWH166,HBWH164,HBWH163,HBWH162,HBWH158,HBWH154,HBWH151,HBWH142,HBWH139,HBWH138,HBWH133,HBWH131,HBWH128,HBWH124,HBWH121,HBWH116,HBWH112,HBWH111,HBWH105,HBWH102,HBWH099,HBWH098,HBWH086,HBWH084,HBWH080,HBWH077,HBWH059,HBWH045,HBWH039,HBWH033,HBWH028,HBWH027,HBWH025,HBWH023,HBWH022,HBWH019,HBWH012,HBWH008,HBSZ003,HBSY004,HBSY003,HBSY001,HBJZ019,HBJZ018,HBJZ017,HBJZ013,HBJZ004,HBJM005,HBHS032,HBHS031,HBHS029,HBHS026,HBHS025,HBHS020,HBHS018,HBHS016,HBHS015,HBHG008,HBEZ001,HBBD003,GZGY059,GZGY058,GZGY057,GZGY056,GZGY055,GZGY053,GZGY050,GZGY044,GZGY041,GZGY039,GZGY038,GZGY037,GZGY033,GZGY013,GZGY010,GZGY007,GZGY005,GZGY004,GZGY003,GXYL019,GXNN038,GXNN037,GXNN033,GXNN032,GXNN031,GXNN030,GXNN018,GXNN017,GXNN007,GXNN004,GXLZ021,GXLZ020,GXLZ019,GXLZ018,GXLZ017,GXLZ014,GXLZ007,GXLZ006,GXLZ004,GXGL040,GXGL038,GXGL036,GXGL035,GXGL034,GXGL032,GXGL031,GXGL030,GXGL013,GXGL012,GXGL011,GXGL002,GXGG002,GXBH003,GXBH001,GSLZ028,GSLZ025,GSLZ015,GSLZ006,GSLZ005,GSLZ004,GSLZ003,GSLZ002,GDZS018,GDZS017,GDZS016,GDZS014,GDZS001,GDZH008,GDZH007,GDZH001,GDSZ047,GDSZ046,GDSZ045,GDSZ044,GDSZ043,GDSZ042,GDSZ040,GDSZ038,GDSZ037,GDSZ036,GDSZ032,GDSZ017,GDSZ013,GDSZ004,GDSZ002,GDSZ001,GDJY011,GDJY010,GDJY009,GDJM010,GDJM009,GDHZ012,GDHZ011,GDHZ008,GDGZ089,GDGZ088,GDGZ087,GDGZ086,GDGZ085,GDGZ084,GDGZ083,GDGZ082,GDGZ081,GDGZ079,GDGZ077,GDGZ076,GDGZ075,GDGZ074,GDGZ073,GDGZ071,GDGZ070,GDGZ068,GDGZ066,GDGZ064,GDGZ062,GDGZ061,GDGZ047,GDGZ042,GDGZ023,GDGZ021,GDGZ006,GDGZ004,GDFS039,GDFS038,GDFS033,GDFS031,GDFS021,GDFS017,GDFS004,GDFS001,GDDW023,GDDW008,GDDW003,GDDG075,GDDG074,GDDG073,GDDG071,GDDG067,GDDG059,GDDG039,FJZZ029,FJZZ028,FJZZ025,FJZZ001,FJXM064,FJXM063,FJXM062,FJXM022,FJXM016,FJXM010,FJQZ025,FJQZ024,FJQZ023,FJQZ022,FJQZ017,FJQZ012,FJPT004,FJPT003,FJPT001,FJLY001,FJFZ043,FJFZ041,FJFZ040,FJFZ037,FJFZ033,FJFZ022,DW407806,DW400478,DW399077,DW395285,DW381615,DW377180,DW368707,DW364714,DW143916,DW143060,DW137429,DW13694,DW126529,DW121990,DW121108,DW118851,DW106708,DW102873,DW098474,DW097703,DW087154,DW083144,DW078807,DW077430,DW071447,DW069691,DW066354,DW065966,DW059329,DW048326,DW042775,DW037572,DW024946,DW024855,DW021684,DW018021,DW014837,DW014059,DW014057,DW014033,DW013990,DW013982,DW013967,DW013948,DW013913,DW013896,DW013889,DW013874,DW013847,DW013816,DW013798,DW013761,DW013742,DW013740,DW013730,DW013658,DW013601,DW013560,DW013529,DW013522,DW013509,DW013442,DW013438,DW013346,DW013341,DW013327,DW013318,DW013256,DW013245,DW013228,DW013211,DW010285,DW004292,CQCQ142,CQCQ140,CQCQ138,CQCQ137,CQCQ136,CQCQ135,CQCQ134,CQCQ132,CQCQ131,CQCQ125,CQCQ122,CQCQ121,CQCQ117,CQCQ116,CQCQ113,CQCQ110,CQCQ108,CQCQ107,CQCQ106,CQCQ101,CQCQ099,CQCQ097,CQCQ094,CQCQ092,CQCQ086,CQCQ085,CQCQ084,CQCQ083,CQCQ082,CQCQ081,CQCQ080,CQCQ077,CQCQ067,CQCQ061,CQCQ044,CQCQ039,CQCQ029,CQCQ028,CQCQ026,CQCQ022,CQCQ021,CQCQ018,CQCQ006,CQCQ005,CQCQ002,BXJWLMQ1364,BXJWLMQ1300,BXJWLMQ1242,BXJWLMQ1082,BXJWLMQ1042,BXJWLMQ0787,BXJWLMQ0682,BXJWLMQ0545,BXJWLMQ0532,BXJWLMQ0528,BTJTJ1575,BTJTJ1552,BTJTJ1486,BTJTJ1469,BTJTJ1467,BTJTJ1408,BTJTJ1398,BTJTJ1327,BTJTJ1209,BTJTJ1200,BTJTJ1180,BTJTJ1178,BTJTJ1062,BTJTJ0958,BTJTJ0942,BTJTJ0852,BTJTJ0793,BTJTJ0671,BTJTJ0649,BTJTJ0614,BTJTJ0603,BTJTJ0536,BTJTJ0530,BTJTJ0527,BTJTJ0488,BTJTJ0448,BTJTJ0422,BTJTJ0184,BTJTJ0064,BSXYC0643,BSXXY1464,BSXXY1406,BSXXY0813,BSXXY0564,BSXXA1623,BSXXA1617,BSXXA1612,BSXXA1611,BSXXA1610,BSXXA1593,BSXXA1592,BSXXA1587,BSXXA1586,BSXXA1574,BSXXA1573,BSXXA1569,BSXXA1568,BSXXA1567,BSXXA1537,BSXXA1517,BSXXA1516,BSXXA1515,BSXXA1514,BSXXA1513,BSXXA1511,BSXXA1503,BSXXA1496,BSXXA1492,BSXXA1483,BSXXA1450,BSXXA1449,BSXXA1446,BSXXA1442,BSXXA1417,BSXXA1384,BSXXA1367,BSXXA1363,BSXXA1357,BSXXA1356,BSXXA1355,BSXXA1354,BSXXA1320,BSXXA1304,BSXXA1284,BSXXA1277,BSXXA1276,BSXXA1270,BSXXA1268,BSXXA1257,BSXXA1254,BSXXA1253,BSXXA1219,BSXXA1206,BSXXA1203,BSXXA1027,BSXXA1024,BSXXA1021,BSXXA1020,BSXXA1011,BSXXA0981,BSXXA0980,BSXXA0957,BSXXA0924,BSXXA0880,BSXXA0860,BSXXA0846,BSXXA0844,BSXXA0828,BSXXA0817,BSXXA0789,BSXXA0738,BSXXA0404,BSXXA0211,BSXXA0050,BSXWN1325,BSXTY1630,BSXTY1585,BSXTY1584,BSXTY1570,BSXTY1551,BSXTY1505,BSXTY1466,BSXTY1453,BSXTY1303,BSXTY1259,BSXTY1229,BSXTY0796,BSXTY0795,BSXTY0769,BSXTY0768,BSXTY0573,BSXTY0547,BSXTY0067,BSXSZ1402,BSXLF1326,BSXBJ1628,BSDZZ1578,BSDZB1590,BSDZB1572,BSDZB1542,BSDZB1447,BSDZB1298,BSDZB0565,BSDYT1522,BSDYT1199,BSDYT0759,BSDYT0748,BSDYT0718,BSDWH0465,BSDWH0464,BSDWF1039,BSDWF0952,BSDTZ1391,BSDTA1558,BSDTA1530,BSDTA1045,BSDRZ1278,BSDQD1445,BSDQD1444,BSDQD1407,BSDQD1365,BSDQD1280,BSDQD1279,BSDQD1233,BSDQD1018,BSDQD0666,BSDQD0584,BSDLY1543,BSDLY1190,BSDJN1559,BSDJN1509,BSDJN1508,BSDJN1506,BSDJN1421,BSDJN1416,BSDJN1349,BSDJN1236,BSDJN1129,BSDJN0960,BSDJN0954,BSDJN0785,BSDJN0560,BSDJN0469,BSDJN0109,BSDJN0070,BSDHZ0826,BSDDZ1624,BQHXN1615,BQHXN1564,BQHXN1512,BQHXN1405,BQHXN1347,BQHXN1346,BQHXN1138,BQHXN1095,BQHXN0841,BQHXN0825,BQHXN0770,BQHXN0722,BQHXN0540,BQHXN0533,BNXZW0832,BNXYC1563,BNXYC1562,BNXYC1561,BNXYC1510,BNXYC1463,BNXYC1426,BNXYC1404,BNXYC1344,BNXYC1258,BNXYC1216,BNXYC1167,BNXYC1092,BNXYC1084,BNXYC0903,BNXYC0884,BNXYC0831,BNXYC0626,BNXYC0562,BNXYC0432,BNXYC0392,BNXYC0325,BNXWZ0498,BNXWZ0497,BNMHHHT0716,BNMHHHT0566,BNMHHHT0423,BNMHHHT0393,BNMHHHT0266,BNMCF1220,BNMCF0928,BNMBT1385,BNMBT1335,BNMBT0715,BLNTL0875,BLNSY1603,BLNSY1539,BLNSY1491,BLNSY1471,BLNSY1455,BLNSY1334,BLNSY1046,BLNSY0987,BLNSY0949,BLNSY0702,BLNSY0698,BLNSY0574,BLNSY0490,BLNKY0641,BLNJZ1547,BLNJZ1523,BLNJZ1498,BLNJZ1137,BLNJZ1034,BLNJZ0644,BLNDL1535,BLNDL1360,BLNDL1341,BLNDL0945,BLNDL0940,BLNDL0882,BLNDL0726,BLNDL0635,BLNDL0595,BLNDL0419,BLNDL0251,BLNAS1501,BLNAS1379,BJLJL1377,BJLCC1224,BJLCC1187,BJLCC1169,BJLCC1159,BJLCC0976,BJLCC0913,BJLCC0798,BJLCC0743,BJLCC0295,BJBJ305,BJBJ300,BJBJ247,BJBJ245,BJBJ211,BJBJ202,BJBJ119,BJBJ114,BJBJ109,BJBJ090,BJBJ087,BJBJ082,BJBJ067,BJBJ066,BJBJ062,BJBJ057,BJBJ056,BJBJ054,BJBJ049,BJBJ048,BJBJ041,BJBJ038,BJBJ029,BJBJ027,BJBJ025,BJBJ011,BJBJ008,BJBJ007,BJBJ004,BJBJ002,BJBJ001,BHLJSH1548,BHLJMDJ1292,BHLJHEB1597,BHLJHEB1549,BHLJHEB1383,BHLJHEB1372,BHLJHEB1343,BHLJHEB1332,BHLJHEB0935,BHLJHEB0800,BHLJHEB0594,BHLJHEB0582,BHLJHEB0264,BHLJHEB0263,BHBXT0513,BHBSJZ1596,BHBSJZ1348,BHBSJZ1310,BHBSJZ1309,BHBSJZ1302,BHBSJZ1223,BHBSJZ1212,BHBSJZ0919,BHBSJZ0873,BHBSJZ0495,BHBSJZ0329,BHBSJZ0324,BHBQHD1436,BHBQHD0805,BHBQHD0585,BHBQHD0459,BHBHD1249,BHBCD1311,BHBBD0858,BHBBD0520,BHBBD0321,BGSLZ1616,BGSLZ1577,BGSLZ1428,BGSLZ1424,BGSLZ1307,BGSLZ1299,BGSLZ1173,BGSLZ0994,BGSLZ0979,BGSLZ0978,BGSLZ0956,BGSLZ0955,BGSLZ0933,BGSLZ0927,BGSLZ0812,BGSLZ0694,BGSLZ0576,BGSLZ0431,BGSLZ0402,BGSLZ0296,BGSLZ0014,BBSWH20019,BBSSH20007,BBSSH20001,BBSSCCD20015,BBSNSXTY10001,BBSLN20008,BBSLN20005,BBSJSSZ20001,BBSHEN20005,BBSHEB20004,BBSCQCQ20004,BBJBJ520,BBJBJ1621,BBJBJ1618,BBJBJ1583,BBJBJ1579,BBJBJ1556,BBJBJ1555,BBJBJ1554,BBJBJ1546,BBJBJ1545,BBJBJ1544,BBJBJ1528,BBJBJ1527,BBJBJ1520,BBJBJ1519,BBJBJ1494,BBJBJ1489,BBJBJ1487,BBJBJ1484,BBJBJ1482,BBJBJ1479,BBJBJ1478,BBJBJ1433,BBJBJ1432,BBJBJ1418,BBJBJ1397,BBJBJ1396,BBJBJ1386,BBJBJ1368,BBJBJ1338,BBJBJ1336,BBJBJ1329,BBJBJ1317,BBJBJ1316,BBJBJ1315,BBJBJ1265,BBJBJ1264,BBJBJ1255,BBJBJ1238,BBJBJ1191,BBJBJ1171,BBJBJ1145,BBJBJ1116,BBJBJ1093,BBJBJ1071,BBJBJ1054,BBJBJ1044,BBJBJ0985,BBJBJ0853,BBJBJ0838,BBJBJ0821,BBJBJ0802,BBJBJ0665,BBJBJ0638,BBJBJ0637,BBJBJ0611,BBJBJ0558,BBJBJ0557,BBJBJ0556,BBJBJ0552,BBJBJ0525,BBJBJ0482,BBJBJ0450,BBJBJ0006,ANWH026,ANWH024,ANWH023,ANWH022,ANWH020,ANWH019,ANWH011,ANWH006,ANWH004,ANWH003,ANTL008,ANMAS014,ANMAS013,ANMAS005,ANMAS001,ANLA010,ANLA006,ANLA004,ANLA003,ANHF063,ANHF062,ANHF061,ANHF060,ANHF059,ANHF058,ANHF057,ANHF056,ANHF055,ANHF053,ANHF051,ANHF049,ANHF048,ANHF047,ANHF044,ANHF042,ANHF039,ANHF035,ANHF034,ANHF026,ANHF019,ANHF013,ANHF010,ANHF008,ANFY034,ANFY032,ANFY002,AHWH028,AHWH027,AHSZ022,AHSZ009,AHHN022,AHHB100,AHBZ030,AHBB022,AHBB020,AHAQ007'
select * from dbo.STRING_SPLIT(@sql,',')
If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLIT function. You can change a compatibility level of database using the following command:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
Note that compatibility level 120 might be default even in new Azure SQL Databases.
For reference:
Version - Highest Compatibility Level - Lowest Available Level
SQL 2017 - 140 - 100
SQL 2016 - 130 - 100
SQL 2014 - 120 - 100
SQL 2012 - 110 - 90
SQL 2008 - 100 - 80
SQL 2005 - 90 - 80
SQL 2000 - 80 - 80
Also, check your syntax as well like:
SELECT Value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
You Can try this function
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
EXAMPLE
DECLARE @StringArray VARCHAR(max)
Set @StringArray= 'a,b,c,d,f';
select * from dbo.fnSplitString(@StringArray,',')
I made this as a quick and dirty substitute using the table approach so the end user can select which of the sections they want. The original string can be used in a join or the individual row selected for a scalar result. Tested in
Microsoft SQL Server 2012 (SP4-OD) (KB4091266) - 11.0.7469.6 (X64) Feb 28 2018 17:47:20 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON; GO
Create FUNCTION dbo.StringSplit2012 ( @OriginalString VARCHAR(500) ,@Separator VARCHAR(6) ) RETURNS @Sections TABLE ( OriginalString VARCHAR(500) NOT NULL ,StringSection VARCHAR(500) NULL ,SectionNumber INT ) AS BEGIN DECLARE @SectionCount INT;
DECLARE @LoopCounter INT = 1;
DECLARE @RemainingString VARCHAR(500);
DECLARE @CurrentSection VARCHAR(500);
SET @SectionCount =
LEN (@OriginalString) - LEN (REPLACE (@OriginalString, @Separator, ''));
IF @SectionCount = 0
BEGIN
INSERT INTO
@Sections
(
OriginalString
,StringSection
,SectionNumber
)
VALUES
(@OriginalString -- OriginalString - varchar(500)
,@OriginalString -- StringSection - varchar(500)
,1 -- SectionNumber - int
);
END;
ELSE
BEGIN
SET @RemainingString = @OriginalString;
DECLARE @SectionStart INT;
DECLARE @SectionLength INT;
WHILE @LoopCounter <= @SectionCount
BEGIN
SET @SectionStart = 1;
SET @SectionLength = CHARINDEX (@Separator, @RemainingString);
SET @CurrentSection = LEFT(@RemainingString, @SectionLength - 1);
INSERT INTO
@Sections
(
OriginalString
,StringSection
,SectionNumber
)
VALUES
(@OriginalString
,@CurrentSection
,@LoopCounter -- SectionNumber - int
);
SET @RemainingString =
RIGHT(@RemainingString, LEN (@RemainingString) - @SectionLength);
SET @LoopCounter = @LoopCounter + 1;
END;
DECLARE @TotalParsedLength INT =
(
SELECT SUM ( LEN (s.StringSection)) FROM @Sections AS s
) + @SectionCount;
SET @CurrentSection =
RIGHT(@RemainingString, LEN (@OriginalString) - @TotalParsedLength);
INSERT INTO
@Sections
(
OriginalString
,StringSection
,SectionNumber
)
VALUES
(@OriginalString
,@CurrentSection
,@LoopCounter -- SectionNumber - int
);
END;
RETURN;
END;
GO
I hope this saves someone some time. I use STRING_SPLIT in the function I created to give me the package name from a job step's command, and it blew up when I moved to my 2012 server. So I wrote my own. (Like ya do!)
Joey Morgan
BI/Integrations Developer III
Aspen Dental Management, Inc
Syracuse, NY
