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