SQL Server - how to split a column with pipe(||) and put into 3 columns in sql server

Asked By The Hunk on 16-May-13 03:22 AM
hai friends ,

i have a problem
i am getting data into a table in single column, like this

prasad||9652575112||12/11/1990
Mahesh||8882796720||02/04/1989
jana||919912513895||11/11/1990

now i want to spilt the column with Pipe(||) and sent into 3 columns in another table.

how to solve this , any suggestions
anshuli replied to The Hunk on 16-May-13 10:10 AM
You can do this by Split function
Sandeep Mittal replied to The Hunk on 16-May-13 10:31 AM
declare @tab table(col varchar(100))
insert into @tab
select 'prasad||9652575112||12/11/1990' union all
select 'Mahesh||8882796720||02/04/1989' union all
select 'jana||919912513895||11/11/1990'
 
select  LEFT(col, pos1-1)
  , SUBSTRING(col, pos1+2, pos2)
  , SUBSTRING(col, pos1+pos2+4, 100) 
from (
  select  CHARINDEX('||', col) as pos1
    , CHARINDEX('||', stuff(col, 1, CHARINDEX('||', col)+2, '')) as pos2
    , col
  from    @tab
) tab