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

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 (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
        set @tmpStr2 = SUBSTRING (@tmpStr , 1,@DelPos - 1 )
        set @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
        set @tmpStr2 = SUBSTRING (@tmpStr , 1,@DelPos - 1 )
        set @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,'') 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

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

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(' ',UserName,1)- 1 )
From
        Tableone

Union all

Select 
        SUBSTRING (UserName , CHARINDEX(' ',UserName,1) +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.