How to split particular column values into rows by using delimiter using sqlserver

Question

Hi to all

how to split particular column values into rows by using delimiter?

Ex:
In a table One column(Name) having Two rows

1)sravan kumar
2)srinivas rao

My requirement is

under column(Name)
i have to display following manner
1)sravan
2)Kumar
3)srinivas
4)rao

help me if you people have any idea regarding this.
any storedprocedure .

Thanks inadvance

srinivas P · Answer

Thank you verymuch i will try this code

mangesh shelar · Answer

Hi here is the query you can use  currently it is for UserName and Address if you have more column then copy and past the section used for address and change the table and column name accordingly -- Create table for Testing create table #temp12 (Id int UserName varchar(1000)  Address varchar(1000)) -- Insert test Data insert into #temp12 (idUserName Address ) values (1'sri ni vasa Rao''hyderabad plotno:109 Dsnr') insert into #temp12 (idUserName Address ) values (2'Sravan kumar''Guntur HNo:1001') insert into #temp12 (idUserName Address ) values (3'sandhya''Guntur') --select * from #temp12 --- Start --drop table #tempname --drop table #tempaddr Declare @Id int Declare @UserName Varchar(1000) Declare @Address Varchar(1000) Declare @tmpStr varchar(1000) Declare @tmpStr2 varchar(1000) Declare @DelPos int Declare @Cnt int DECLARE CurSplitLoop CURSOR FOR SELECT ID UserName Address FROM #temp12 OPEN CurSplitLoop FETCH NEXT FROM CurSplitLoop INTO @Id @UserName  @Address --Create table #temploop (Id int UserName varchar(1000)  Address varchar(1000) ColOrder int) Create table #tempName (Id int UserName varchar(1000)  ColOrder int) Create table #tempAddr (Id int Address varchar(1000) ColOrder int) WHILE @@FETCH_STATUS = 0 BEGIN --Split UserName set @tmpStr = @UserName set @DelPos = CHARINDEX(' '@tmpStr1) set @Cnt = 1 while @DelPos > 0 begin set @tmpStr2 = SUBSTRING (@tmpStr  1@DelPos - 1 ) set @tmpStr = SUBSTRING (@tmpStr  @DelPos + 1  8000) set @DelPos = CHARINDEX(' '@tmpStr1) insert into #tempName(Id UserName ColOrder) values (@Id  @tmpStr2  @Cnt ) set @Cnt = @Cnt + 1 end insert into #tempName(Id UserName  ColOrder) values (@Id  @tmpStr  @Cnt ) -- split Address set @tmpStr = @Address set @DelPos = CHARINDEX(' '@tmpStr1) set @Cnt = 1 while @DelPos > 0 begin set @tmpStr2 = SUBSTRING (@tmpStr  1@DelPos - 1 ) set @tmpStr = SUBSTRING (@tmpStr  @DelPos + 1  8000) set @DelPos = CHARINDEX(' '@tmpStr1) insert into #tempAddr (Id Address  ColOrder) values (@Id  @tmpStr2  @Cnt ) set @Cnt = @Cnt + 1 end insert into #tempAddr(Id Address  ColOrder) values (@Id  @tmpStr  @Cnt ) FETCH NEXT FROM CurSplitLoop INTO @Id @UserName  @Address END CLOSE CurSplitLoop DEALLOCATE CurSplitLoop --select * from #tempName --select * from #tempAddr select n.Idn.UserName  isnull(a.Address'') from #tempName n left outer join #tempAddr a on n.Id = a.Id and n.ColOrder = a.ColOrder -- Drop the Temporary table created drop table #tempname drop table #tempaddr

srinivas P · Answer

Thanks for Your quick reply I am very beginner to create temptables In my requirement Particular column can have more than one Values(mean that column have morethan one rows) how to create temp table for this help me

mangesh shelar · Answer

Try this query below it has space as delimiter as per your example can replace it with any other delimiter this will work if you have 2 names in each columns . Select SUBSTRING (UserName  1 CHARINDEX(' 'UserName1)- 1 ) From Tableone Union all Select SUBSTRING (UserName  CHARINDEX(' 'UserName1) +1  8000) From Tableone if you can have only one name in a column then you need to handle it using case in the above query and if you can have any number of names then you can create a temp table and run in look and get the result  Let me know if you need any more help on this.