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


Related Articles

How to get rows into comma separated values column in sql

How to get rows into comma separated values column in sql. Fetching rows values into comma separated column value is required lots of time in our projects. So I tried to achieve this and lastly found one solution. Use this to get all row values into one single column value with comma separation.

More articles: Comma separated value

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: