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

-- Create table for Testing
create table #temp12 (Id int, UserName varchar(1000) , Address varchar(1000))

-- Insert test Data
insert into #temp12 (id,UserName, Address ) values (1,'sri ni vasa Rao','hyderabad plotno:109 Dsnr')
insert into #temp12 (id,UserName, Address ) values (2,'Sravan kumar','Guntur HNo:1001')
insert into #temp12 (id,UserName, 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(' ',@tmpStr,1)    
    set @Cnt = 1
    while @DelPos > 0
    begin
        select @tmpStr2 = SUBSTRING (@tmpStr , 1,@DelPos - 1 )
        , @tmpStr = SUBSTRING (@tmpStr , @DelPos + 1 , 8000)
        set @DelPos = CHARINDEX(' ',@tmpStr,1)    
        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(' ',@tmpStr,1)    
    set @Cnt = 1
    while @DelPos > 0
    begin
        select @tmpStr2 = SUBSTRING (@tmpStr , 1,@DelPos - 1 )
        , @tmpStr = SUBSTRING (@tmpStr , @DelPos + 1 , 8000)
        set @DelPos = CHARINDEX(' ',@tmpStr,1)    
        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.Id,n.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

How to pass column name to split function

i.e  my requirement is,how many rows are kept under 'column' all are split into rows

help me
Thanks inadvance

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

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)<1 or @String is null  return 
 
  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(@String,len(@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

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