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)        
  declare @xml xml        
  set @xml = N'<root><r><![CDATA[' + replace(@delimited,@delimiter,']]></r><r><![CDATA[') + ']]></r></root>'        
  insert into @t(val)        
    r.value('.','varchar(max)') as item        
  from @xml.nodes('//root/r') as records(r)        

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

cde and cdf

What I have tried:

Please help.
Split a comma sperated string in Sql server with []
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.
Maciej Los 26-Apr-17 16:38pm

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