Splitting One Column into 4 columns 1

[Andel]

Hello, I have a table with one column and i want to split it into 4 columns. Values are comma delimeted. Is there a way using SQL query alone?

Column1
======================
C,18432,6196,12236
E,409595,112464,297131
H,407548,11915,395633


I want the output to be:

Drive Capacity Used Free
C 18432 6196 12236
E 409595 112464 297131
H 407548 11915 395633

Any idea?

thanks

 

[mrdenny]

Yes. You will want to use SUBSTRING and CHARINDEX.

Take a look in Books OnLine for the syntax. If you need some assistance after that let us know.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Qik3Coder]

faq183-1151

'cause I'm lazy and don't like typing.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Andel]

Yes i tried charindex along with substring. i can do it cleanly for the first 2 columns, "drive" and "capacity", but the query becomes ugly after that. The challenge is how to get the starting and ending position. Can you give me your code?

I can use cursor with a while loop also but that's my last resort.

Thanks

 

[Qik3Coder]

Andel, read the FAQ, just copy paste it into Q/A and hit run, you will get the idea.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Andel]

Thanks Qik3Coder. I'm actually looking for a single select without a while loop. If it can't be done, then I go with the while loop as in your example. But in my case, i need a loop inside a loop. Outer loop is to go through the rows and inner loop is for splitting the columns, just like your example. But like I said, that will be my last resort.

Any other idea?

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Col1 [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'C,18432,6196,12236'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'E,409595,112464,297131'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'H,407548,11915,395633'[/color])

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]LEFT[/color](Col1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],Col1)-1),
       [COLOR=#FF00FF]SUBSTRING[/color](Col1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],Col1)+1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],Col1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],Col1)+1)-[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],Col1)-1),
       [COLOR=#FF00FF]REVERSE[/color]([COLOR=#FF00FF]SUBSTRING[/color]([COLOR=#FF00FF]REVERSE[/color](Col1),[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],[COLOR=#FF00FF]REVERSE[/color](Col1))+1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],[COLOR=#FF00FF]REVERSE[/color](Col1),[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],[COLOR=#FF00FF]REVERSE[/color](Col1))+1)-[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],[COLOR=#FF00FF]REVERSE[/color](Col1))-1)),
       [COLOR=#FF00FF]RIGHT[/color](Col1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]','[/color],[COLOR=#FF00FF]REVERSE[/color](Col1))-1)
[COLOR=blue]FROM[/color] @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

If the most you will have is 4 parts (3 commas), you can use the ParseName function. Like this...

DECLARE @Test TABLE (Col1 varchar(200))
INSERT INTO @Test VALUES ('C,18432,6196,12236')
INSERT INTO @Test VALUES ('E,409595,112464,297131')
INSERT INTO @Test VALUES ('H,407548,11915,395633')

Select ParseName(AliasName, 4) as Drive,
       ParseName(AliasName, 3) As Capacity,
       ParseName(AliasName, 2) As Used,
       ParseName(AliasName, 1) As Free
From   (
       Select Replace(Col1, ',', '.') As AliasName
       From   @Test
       ) As DerivedTableAlias

ParseName is designed to split parts based on dots. So, if your original data already has dots (like decimal places in the data), then this will not work for you. However, under tightly controlled circumstances, this method should work just fine.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

I know George, Boris and QikCoder are gods here, but just a couple of thoughts.

1. what is populating the table with a string full of commas? Why not get the import routine right defining a CSV as the input file?
2. Design the table to receive the 4 columns as opposed to 1 string?

I use Win_32 calls to get the disk data for 40 odd servers and it goes to a text file, then it is imported into a table.

Cheers,

M.

 

[Andel]

gmmastros, you're great!
thanks a lot. that's exactly what i'm looking for.

thanks also to others who responded.