Split Comma separated values into Columns


In this article, I explain about how to split a string with delimiter into list of rows. De limiter can be comma or plus our requirement is to convert a single row of a table into multiple rows based on number of delimiter.

Split function using a Query

Let us see a simple Example to split a string with delimiter


DECLARE @Keyword TABLE(Keywords varchar(100))
insert into @Keyword values('Chennai ,Bangalore')

select Keywords,substring(Keywords,0,charindex(',',Keywords))Key1
,SUBSTRING(Keywords,charindex(',',Keywords)+1,LEN(Keywords)-charindex(',',Keywords))Key2
from @Keyword


Split function using CTE

For multiple delimiter in a single row we can use CTE to split a string


DECLARE @Keyword TABLE(ID INT IDENTITY(1,1), Keywords varchar(100))
insert into @Keyword values('Chennai ,Bangalore ,Mysore ,Delhi')
insert into @Keyword values('Business Solutions ,Business Solutions in Bangalore ,Business Solutions in Mysore')

;with SplitString as
(
select ID,Keywords,LEFT(Keywords,charindex(',',Keywords)-1)Keys
,RIGHT(Keywords,LEN(keywords)-charindex(',',Keywords))Remaining_Keys
from @Keyword where Keywords is Not null and charindex(',',Keywords)>0

UNION ALL
select ID,Keywords,LEFT(Remaining_Keys,charindex(',',Remaining_Keys)-1)
,RIGHT(Remaining_Keys,LEN(Remaining_Keys)-charindex(',',Remaining_Keys))
from SplitString where Keywords is Not null and charindex(',',Remaining_Keys)>0

UNION ALL
select ID,Keywords,Remaining_Keys,NULL
from SplitString where Keywords is Not null and charindex(',',Remaining_Keys)=0
)
select ID,Keywords,Keys from SplitString order by ID


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: