Click here to Skip to main content
14,734,150 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
select distinct val from dbo.Split('[Country],[Created By],[Date of Birth],[High School],[If Other, What Country?],[If Other- What Country?],[Middle Name],[Preferred First Name],[Sex]',',')

This is my function that splits by comma.
CREATE FUNCTION [dbo].[Split]    
(        
  @delimited nvarchar(max),        
  @delimiter nvarchar(100)        
) RETURNS @t TABLE        
(        
  val nvarchar(max)        
)        
AS        
BEGIN        
  declare @xml xml        
  set @xml = N'<root><r><![CDATA[' + replace(@delimited,@delimiter,']]></r><r><![CDATA[') + ']]></r></root>'        
        
  insert into @t(val)        
  select        
    r.value('.','varchar(max)') as item        
  from @xml.nodes('//root/r') as records(r)        
        
  RETURN        
END


I want to split the string outside the [abc],[cde,cdf]
into abc and cde,cdf but its splitting into

abc,
cde and cdf

What I have tried:

Please help.
Split a comma sperated string in Sql server with []
Posted
Updated 25-Apr-17 23:11pm

1 solution

Split on ] instead of comma, replace the [ and handle the comma at the start of an item if it exists...
DECLARE @x nvarchar(max) = '[abc],[cde,cdf]'
;with q as
(
	select REPLACE(val, '[','') as val2
	from dbo.Split(@x, ']')
	where isnull(val,'') <> ''
)
select case when SUBSTRING(val2,1,1)=',' THEN SUBSTRING(val2, 2, LEN(val2))
		else val2 END
from q
I only used a CTE (or a sub-query would do it) to avoid multiple calls to the split function.
   
Comments
Maciej Los 26-Apr-17 16:38pm
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900