Post reply

split a long string column into separate columns

  • October 22, 2009 at 12:52 pm

    #216558

    Hi All,

    Is there any way to split this long string column separated by DOTS

    from source into severals columns in target

    For Ex:

    LONGcolumn

    ab.cd.ef.gh

    Target should be:

    Col1 Col2 Col3 Col4

    ab cd ef gh

    Thanks in Advance

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • October 22, 2009 at 1:56 pm

    #1069643

    if there are exactly 3 decimals/4 parts in your string, you can use the built in function PARSENAME.

    otherwise, it's the same idea, just more involved by using CHARINDEX and SUBSTRING

    declare @longcolumn varchar(max)

    SET @longcolumn = 'ab.cd.ef.gh'

    select

    PARSENAME(@longcolumn,4) As col1,

    PARSENAME(@longcolumn,3) As col2,

    PARSENAME(@longcolumn,2) As col3,

    PARSENAME(@longcolumn,1) As col4

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 22, 2009 at 8:38 pm

    #1069750

    Lowell,

    Nice!!! I've never seen this in use before but it looks like it could work nice with IP addresses and subnet masking. Where did you ever learn this man? I looked at BOL's info on PARSENAME and would have never thought to use it in this way. I guess it only works for strings that have up to 4 .'s. I love learning new tricks!

    -Impressed!

  • October 22, 2009 at 9:44 pm

    #1069770

    it's actually usually used for parsing up a Server.Database.Schema.Tablename object, but like you identified, works perfect for IP addresses as well; anything that is getting chopped up by periods; I've used it for fileversion stuff as well: you know. version 9.0.101.0 major /minor/etc

    I think it was Jeff Moden who showed me some of the alternate uses a couple of years ago right here on SSC; just one more tool in the toolbox!

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 22, 2009 at 11:09 pm

    #1069788

    Thanks for the kudo, Lowell.

    --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)

  • October 25, 2009 at 6:21 pm

    #1070471

    Thanks Lowell...

    It works perfectly f9....

    Thk uuu again:-):-)

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

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

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