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
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
from @Keyword where Keywords is Not null and charindex(',',Keywords)>0

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

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


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: