Post reply

Split column data into 2 columns

  • 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?

  • July 13, 2009 at 3:59 am

    #1023762

    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

  • July 13, 2009 at 4:00 am

    #1023763

    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

  • July 13, 2009 at 4:45 am

    #1023774

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

  • July 13, 2009 at 5:52 am

    #1023802

    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

  • February 12, 2010 at 12:22 pm

    #1118222

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

    Thanks

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

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