Post reply

splitting a string into columns

  • October 8, 2013 at 9:53 am

    #278831

    Hi All ,

    i am having a string i want output in 3 separate columns

    'A-111:B-2222:C-33333'

    A B C

    111 2222 333333

    'A-111:B-2222'

    A B C

    111 2222 -

    'A-111'

    A B C

    111 - -

    'B-2222'

    A B C

    - 222 -

    'B-2222:C-33333'

    A B C

    - 222 33333

    How can i best achieve this using STUFF or by using split function in SQL Server.

    Thanx

    VD

  • October 8, 2013 at 10:07 am

    #1656809

    I hope that you're trying to eliminate this horrible design to have a properly normalized table.

    You can accomplish using the 8KDelimitedSplitter[/url] and CROSS TABS[/url]

    SELECT MAX( CASE WHEN LEFT( Item, 1) = 'A' THEN SUBSTRING( Item, 3, 100) ELSE '-' END) A,

    MAX( CASE WHEN LEFT( Item, 1) = 'B' THEN SUBSTRING( Item, 3, 100) ELSE '-' END) B,

    MAX( CASE WHEN LEFT( Item, 1) = 'C' THEN SUBSTRING( Item, 3, 100) ELSE '-' END) C

    FROM (VALUES

    ('A-111:B-2222:C-33333' ),

    ('A-111:B-2222' ),

    ('A-111'),

    ('B-2222' ),

    ('B-2222:C-33333' ))x(String)

    CROSS APPLY dbo.DelimitedSplit8K( String, ':')

    GROUP BY String

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 9, 2013 at 6:21 am

    #1657108

    Thanx a lot ! Let me try this on the Production DB Set.

  • October 9, 2013 at 6:41 am

    #1657116

    vineet_dubey1975 (10/9/2013)

    Thanx a lot ! Let me try this on the Production DB Set.

    Remember that if you execute something on production, you're responsible for it. If something goes wrong, at least know what the code actually does and be able to explain it to someone else.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

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