tag:blogger.com,1999:blog-2322506742556220421.post584833085657304101..comments2023-09-09T01:11:08.803-07:00Comments on THE CODE BEHIND: Split string using multiple delimiters in SQL SERVERArunprasanth K Vhttp://www.blogger.com/profile/13093114211601525370noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-2322506742556220421.post-80498643985478914582016-09-01T06:04:05.373-07:002016-09-01T06:04:05.373-07:00Hi sorry for the delay. You can refer Asma shaik c...Hi sorry for the delay. You can refer Asma shaik comment for doing the same. But if you have lots of delimiters and you don't want to append that delimiters with your strings then you can simply replace all your delimiter with a single delimiter and use can use the above function <br />Sample :<br /><br />input : abcd_def.ghi+klo-ll: <br />output: abcd<br />def<br />ghi<br />klo<br />ll<br /><br />you can use the below method for that <br />declare @abc nvarchar(4000) ;<br /> set @abc=(select<br /> <br /> replace(replace(replace(replace('abcd_def.ghi+klo-ll', '_', '_'), '.', '_'), '+', '_'), '-', '_')) <br /><br /><br /> Select * From dbo.SplitFunction (@abc,'_')Arunprasanth K Vhttps://www.blogger.com/profile/13093114211601525370noreply@blogger.comtag:blogger.com,1999:blog-2322506742556220421.post-25320948500662838732016-09-01T05:51:45.581-07:002016-09-01T05:51:45.581-07:00Hi Asma if you want to display the delimiter then ...Hi Asma if you want to display the delimiter then you can use the below method <br />alter FUNCTION dbo.MultipleDelemiterSplit<br />(<br /> @List NVARCHAR(MAX),<br /> @Delemiter1 Varchar(100),<br /> @Delemiter2 Varchar(100)<br />)<br />RETURNS TABLE<br />AS<br /><br /> RETURN<br /> ( <br /> SELECT Item = FirstSet.cnt.value('(./text())[1]', 'nvarchar(4000)')<br /> FROM<br /> (<br /> SELECT x = CONVERT(XML, ''<br /> + REPLACE(REPLACE(@List, ISNULL(@Delemiter1,''),''+ @Delemiter1) , ISNULL(@Delemiter2,''), ''+@Delemiter2)<br /> + '').query('.')<br /> ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)<br /> );<br /><br />GO Arunprasanth K Vhttps://www.blogger.com/profile/13093114211601525370noreply@blogger.comtag:blogger.com,1999:blog-2322506742556220421.post-42607034827878279942016-08-29T05:39:56.759-07:002016-08-29T05:39:56.759-07:00Also I want to display the delimeter also to be di...Also I want to display the delimeter also to be displayed.....could you please suggestphttps://www.blogger.com/profile/05637888338609694643noreply@blogger.comtag:blogger.com,1999:blog-2322506742556220421.post-85431119566720400122016-08-29T05:31:22.627-07:002016-08-29T05:31:22.627-07:00I have used below code to use 7 delimiters.....
he...I have used below code to use 7 delimiters.....<br />here the data (i.e; string which needs to be split ) we are sending hardcoded...I want the table data to pass this to a function...how can i do this<br /><br />SELECT x = CONVERT(XML, ''<br /> + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(<br /> REPLACE(@List, ISNULL(@Delemiter1,''),<br /> '') ,<br /> ISNULL(@Delemiter2,''), <br /> ''),ISNULL(@Delemiter3,''),''),<br /> ISNULL(@Delemiter4,''),''),<br /> ISNULL(@Delemiter5,''),''),<br /> ISNULL(@Delemiter6,''),''),<br /> ISNULL(@Delemiter7,''),'')<br /> + '').query('.') <br /> <br /> <br /> <br /> ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)phttps://www.blogger.com/profile/05637888338609694643noreply@blogger.comtag:blogger.com,1999:blog-2322506742556220421.post-36775051202008383622016-08-29T05:08:09.719-07:002016-08-29T05:08:09.719-07:00can you please send the modified code which accep...can you please send the modified code which accepts more than 2 delimeters..like 5 to 6 delimitersphttps://www.blogger.com/profile/05637888338609694643noreply@blogger.comtag:blogger.com,1999:blog-2322506742556220421.post-65356408269479911462016-03-08T11:57:23.605-08:002016-03-08T11:57:23.605-08:00Nice Article !
Really this will help to people of...Nice Article !<br /><br />Really this will help to people of SQL Server Community. <br />I have also prepared small note on this, function to splie string using different delimiters in SQL Server.<br /><br />http://www.dbrnd.com/2015/12/sql-serverfunction-to-split-string-value-using-different-delimiters/<br />Anonymoushttps://www.blogger.com/profile/01365030762397385247noreply@blogger.com