splitting a string into columns

Question

Post reply

splitting a string into columns

vineet_dubey1975

SSCrazy

Points: 2306
More actions
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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

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