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