Post reply

How to place IP address(one field) into different fields.

  • December 6, 2008 at 8:35 pm

    #154993

    Hello everybody,

    I have IP addresses in one field. I need to display each set of values into different fields.

    For example, in an 'IP_Addresses' field I have a record as this:

    IP_Addresses

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

    208.77.188.166

    Now, I need to display it like this:

    col_A col_B col_C col_D

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

    208 77 188 166

    Can any one give me a clue on how to do this. Thank you.

  • December 6, 2008 at 11:44 pm

    #908902

    Replace the derived table in the from clause with your real table...

    SELECT PARSENAME(d.IpAddr,4) As ColA,

    PARSENAME(d.IpAddr,3) As ColB,

    PARSENAME(d.IpAddr,2) As ColC,

    PARSENAME(d.IpAddr,1) As ColD

    FROM

    (SELECT '208.77.188.166' AS IpAddr UNION ALL

    SELECT '208.77.188.001' UNION ALL

    SELECT '208.77.188.1')d

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 7, 2008 at 11:04 am

    #908951

    Jeff, I would never have thought to use parsename on an IP address, but it makes perfect sense because it has four elements separated by periods. Tell me that wasn't an original idea that just occurred to you when you saw his question.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • December 7, 2008 at 11:26 am

    #908954

    Thank you Jeff.

  • December 8, 2008 at 4:54 am

    #909081

    Bob Hovious (12/7/2008)

    Jeff, I would never have thought to use parsename on an IP address, but it makes perfect sense because it has four elements separated by periods. Tell me that wasn't an original idea that just occurred to you when you saw his question.

    Heh... no... it was an "original" idea when I first saw the need back in the 90's... I say "original" because I later found out that lot's of people had been using it for that and I just didn't know it. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 8, 2008 at 4:54 am

    #909082

    Suraj (12/7/2008)

    Thank you Jeff.

    You bet... thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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