How to seprate the values in sql

Question

hello  i m table some values with comma like this id core 1 ITDesign ArtSCO 2 IT with MobileArt with Design penETC i wants to list of comma seprate plz help me and give me idea i wants like this 1----------IT 1---------Design Art 1--------SCO 2------------IT with Mobile 2-------------Art with Design pen 2------------ETC so plz help me

Aarti · Answer

Hi Dharmesh please try with this code: drop table temptable; create table temptable(items varchar(max)) ; declare @index int ; declare @values varchar(max) @SepValues varchar(max)@Delimiter char(1) ; set @SepValues='ITDesign ArtSCOIT with MobileArt with Design penETC' set @Delimiter= '' select @index = 1 if len(@SepValues) while @index!= 0 begin set @index = charindex(@Delimiter@SepValues) if @index!=0 set @values = left(@SepValues@index - 1) else set @values = @SepValues if(len(@values)>0) insert into temptable(Items) values(@values) set @SepValues = right(@SepValueslen(@SepValues) - @index) if len(@SepValues) = 0 break end select * from temptable; Thanks.

Dharmesh sharma · Answer

i try this code and ALTER FUNCTION [dbo].[Split] (@sep char(1) @s varchar(8000)@id int) RETURNS table AS RETURN ( WITH Pieces(pn start stop) AS ( SELECT 1 1 CHARINDEX(@sep @s) UNION ALL SELECT pn + 1 stop + 1 CHARINDEX(@sep @s stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn SUBSTRING(@s start CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s FROM Pieces ) i thing its work now but not good for future

Prabhu Raja · Answer

Hi You may try my Blog. http://www.c-sharpcorner.com/Blogs/6476/split-function-in-sql-server-to-break-comma-separated-string.aspx

Vulpes · Answer

Not as easy as it should be in T-SQL but there's a clever Split function in this thread: http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor