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,',')
string_split functionality, you have to comment id row. id is usefull/comfortable when you have to insert into one table two columns from two strings ('a1,b1,c1' to Column1 and 'a2,b2,c2' to Column2 for instance).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')
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, ',')
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)
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
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,',')
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.', ' ');
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
