Post reply

How to split one column into two columns in sql server ?/

  • March 6, 2015 at 7:07 am

    #305058

    HI I have a ipaddress column is there where i need to split the column into two columns because of

    values like below

    172.26.248.8,Fe80::7033:acba:a4bd:f874

    172.26.248.8,Fe80::7033:acba:a4bd:f874

    172.26.248.8,Fe80::7033:acba:a4bd:f874

    I have written the below query but it will throuh some error ,Could you pls make a query and give it me.

    select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0

    from IPADDRESS

    error:

    Msg 537, Level 16, State 2, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

  • March 6, 2015 at 7:26 am

    #1782167

    This uses Jeff Moden's DelimitedSplit8K function posted at http://qa.sqlservercentral.com/articles/Tally+Table/72993/. If you aren't familiar with it yet, take the time to acquainted with it. It's well worth the read and will change the way you look at strings.

    with cteData(string) as (

    select '172.26.248.8,Fe80::7033:acba:a4bd:f874' union all

    select '172.26.248.154,Fe90::7033:acba:a4bd:f874'

    )

    select d.string,

    MAX(case when s.ItemNumber = 1 then s.Item end) ip_address,

    MAX(case when s.ItemNumber = 2 then s.Item end) mac_address

    from cteData d

    cross apply DelimitedSplit8K(d.string, ',') s

    group by d.string;

    Edit: Oops. Added in the original string into the query so you could see each full row and examine the results.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 2 posts - 1 through 1 (of 1 total)

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