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

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.

SQL Script to Concatenate column values

There is frequent need for sql string concatenation to get a list of column values(Comma separated value) of a table. Generally udf's (Used Defined Function) are used to concatenate string in sql. Here is a very simple T-SQL sample using FOR XML PATH() in order concatenate strings values in SQL SERVER

More articles: Comma separated value


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: