How to split single row into multiple rows if row have spaces?

Question

hi to all I want to split single row into multiple Rowsif row have a space.using sql server. Ex:in my table i have one field Name: Srini Vasa rao Iwant to show Name: srini Vasa rao How?Help me

Prabhu Raja · Answer

Check this i gave you a function it will useable with all delimiters. CREATE FUNCTION [dbo].[Split](@String varchar(8000) @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String) while @idx!= 0 begin set @idx = charindex(@Delimiter@String) if @idx!=0 set @slice = left(@String@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@Stringlen(@String) - @idx) if len(@String) = 0 break end return end you can check like this DECLARE d CURSOR FOR Select [name] from table1 DECLARE @rowvalue Varchar(250) OPEN d FETCH NEXT FROM d INTO @rowvalue select * from dbo.split(@rowvalue'') CLOSE d DEALLOCATE d The output will be

saraswathi tanna · Answer

Hello there is there any alternative instead of cursors because cursors may reduce the performance?

mangesh shelar · Answer

-- 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 select @tmpStr2 = SUBSTRING (@tmpStr  1@DelPos - 1 )  @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 select @tmpStr2 = SUBSTRING (@tmpStr  1@DelPos - 1 )  @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'')  n.ColOrder from #tempName n left outer join #tempAddr a on n.Id = a.Id and n.ColOrder = a.ColOrder drop table #tempname drop table #tempaddr

srinivas P · Answer

How to pass column name to split function i.e my requirement ishow many rows are kept under 'column' all are split into rows help me Thanks inadvance

srinivas P · Answer

Thanks for your Quick reply But My table like this Name Address sri ni vasa Rao hyderabad plotno:109 Dsnr Sravan kumar Guntur HNo:1001 sandhya Guntur My Requirement is Name Address sri Hyderabad ni plotNo:109 vasa Dsnr Rao sravan Guntur kumar HNo:1001 sandhya Guntur How to solve this one? Help me

Narayan · Answer

Try to use replace function SELECT REPLACE('abc xyz'' 'char(13)) will show you abc xyz so in your case SELECT REPLACE(NAME' 'char(13)) as NAME FROM TABLE_NAME