Split column data into 2 columns

Question

Post reply

Split column data into 2 columns

Matthew Lockwood

SSC Enthusiast

Points: 112
More actions
July 13, 2009 at 3:42 am

#136507

Hi,
I have the following table:
create table dbo.Results
(id int, ivalue varchar(1000))
insert into dbo.Results
select 1, '1;12345'
union
select 2, '2;212345'
union
select 3, '1;312345'
union
select 4, '1;412345'
union
select 5, '1;512345'
I want to return the ivalue column as 2 columns splitting the data by ';' separator.
ending up with a result of 3 columns. Any tips on the best way to do this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

t.hitendra SSCommitted Points: 1620 More actions · Answer 1

Hi,

Just try this

create table dbo.Results
(id int, ivalue varchar(1000))
insert into dbo.Results
select 1, '1;12345'
union
select 2, '2;212345'
union
select 3, '1;312345'
union
select 4, '1;412345'
union
select 5, '1;512345'
select id,substring(ivalue,0,charindex(';',ivalue)) [ivalue1],substring(ivalue,charindex(';',ivalue)+1,len(ivalue)) [ivalue2] from dbo.REsults
drop table dbo.Results

ps. One Orange Chip Points: 29422 More actions · Answer 2

select id, left(ivalue,charindex(';',ivalue)-1) as ivalue1, right(ivalue,len(ivalue)-charindex(';',ivalue)) as ivalue2 from Results

OUTPUT

-------

id ivalue1 ivalue2

----------- --------- ---------

1 1 12345

2 2 212345

3 1 312345

4 1 412345

5 1 512345

(5 row(s) affected)

Pradeep Singh

Matthew Lockwood SSC Enthusiast Points: 112 More actions · Answer 3

Excellent Just what I was after. Thanks for the quick response!!:-D

k.jayabhaskar Valued Member Points: 70 More actions · Answer 4

If the length of ivalue is fixed, then even the following query will do..

select id, substring(ivalue,1,1), substring(ivalue,3,10)

from Results

- baski

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 5

If there are multiple delimiters in the substring ... how do I modify the query ?

Thanks