Post reply

Derived Columns= Please help- Important

  • March 9, 2012 at 9:52 am

    #258383

    I need help on seperating the following one column information into two columns; For example

    ---Current Data

    PMSA

    (560028) NEW YORK, NY PMSA

    (-9) UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID

    (4) UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID

    ----Need to seperate into two columns

    PMSA_CODE PMSA_DESC

    560028 NEW YORK, NY PMSA

    -9 UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID

    4 UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID

  • March 9, 2012 at 10:33 am

    #1457297

    The only difference between the two sets that I see is that you have removed the parenthesis.

    What are the desired columns?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 9, 2012 at 10:35 am

    #1457299

    Here is one way of performing what I think you want. Now it is not the most efficient way, so if you have 100,000s of rows in your table it will be rather slow.

    CREATE TABLE #T(PMSA_CODE VARCHAR(100))

    INSERT INTO #T (PMSA_CODE)

    SELECT '(560028) NEW YORK, NY PMSA'

    UNION ALL

    SELECT '(-9) UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID' UNION ALL

    SELECT '(4) UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID'

    SELECT SUBSTRING(PMSA_CODE,2,CHARINDEX (')',PMSA_CODE,2)-2)

    ,SUBSTRING(PMSA_CODE,CHARINDEX (')',PMSA_CODE,2)+1,DATALENGTH(PMSA_CODE))

    FROM #T

    -- DROP TABLE #T

    some results:

    560028 NEW YORK, NY PMSA

    -9 UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID

    4 UNDESIGNATED AREA/MISSING/UNKNOWN/NOT COLLECTED/INVALID

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 9, 2012 at 11:24 am

    #1457318

    Thank you so much for reply, but I need to use the substring in SSIS package. If you can provide me the command or transformation option to seperate the different number values to a seperate column that would be greate.

  • March 9, 2012 at 11:29 am

    #1457323

    Use a tsql object and execute the script already provided.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 9, 2012 at 11:36 am

    #1457326

    It looks like you are trying to separate the leading number from the rest of the data. If so, try this.

    SUBSTRING( YourFieldDataHere, 2, FINDSTRING( YourFieldDateHere, ")", 1) -2)

  • March 9, 2012 at 12:12 pm

    #1457344

    Yes, You are my life saver... I will try and let you know. Than ks

  • March 9, 2012 at 12:55 pm

    #1457365

    Thanks a lot! It did work!

Viewing 8 posts - 1 through 7 (of 7 total)

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