String Split in Sql server If your sql Compatibility level Below 130

Question

Do You Know How To Split The Sting Using Sql Server ,If Your Sql Server Compatibility level is Below 130

Rajanikant Hawaldar · Answer

i think you should use XML method below is just sample SELECT Split.a.value( '.' , 'NVARCHAR(MAX)' ) DATA FROM ( SELECT CAST ( ' ' + REPLACE (@ID, ',' , ' ' )+ ' ' AS XML) AS String ) AS A CROSS APPLY String.nodes( '/X' ) AS Split(a); Output 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Jaimin Shethiya · Answer

Hello Ajay, try below query and checked the your database Compatibility level SELECT compatibility_level FROM sys.databases WHERE name = 'Your_Database_Name'; based on that you can check and split your string. Thanks

Ajay Kumar · Answer

declare @nm varchar(50),@nm1 varchar(50),@devicenm varchar(50),@serialno varchar(50) set @nm='Ajay__Abcd__65465465' set @devicenm='' set @serialno='' SELECT @nm1=SUBSTRING(@nm, 0, CHARINDEX('__', @nm)) if(@nm1 '') begin SELECT @devicenm= SUBSTRING(replace( @nm,@nm1+'__',''), 0, CHARINDEX('__', replace( @nm,@nm1+'__',''))) SELECT @serialno= Replace(SUBSTRING(replace( @nm,@nm1+'__',''), CHARINDEX('__', replace( @nm,@nm1+'__','')), len(replace( @nm,@nm1+'__',''))),'__','') select @nm1 as 'NAME', @devicenm as 'DEVICE', @serialno as 'SERIAL NO' end else begin select @nm as 'NAME',@devicenm as 'DEVICE',@serialno as 'SERIAL NO' end

String Split in Sql server If your sql Compatibility level Below 130

Answers (3)