Split a result by commas

Question

Post reply

Split a result by commas

purplebirky

SSC-Addicted

Points: 461
More actions
August 3, 2009 at 9:36 am

#209037

in this table one column has entrys that look like : res1, res2, res3
well i need to split these out to return each on it's own.
[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]

Viewing 11 posts - 1 through 10 (of 10 total)

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

AVB SSChampion Points: 11418 More actions · Answer 1

You can do this a few ways but it depends on the data that's being input.

Do you know what kind of data is going to be input? Will it be from a predetermined list of values?

Will they always follow the same format i.e. 4 characters then a comma Res1,Res2,Res3,Res4, ?

Also what is the max amount of values that can be entered for that column?

You also said they need to be output on their own. Does this mean you want them in a row of their own or in a column of their own (like a pivot table)?

AVB SSChampion Points: 11418 More actions · Answer 2

Here is one way to do it:

declare @Acc varchar(100),@AccString varchar(100)

set @AccString = 'Res1,Res2,Res3,Res4' --or select @AccString = Column from YourTable

--Create Temp Table

IF Object_ID ('#Array') is null

BEGIN

Create Table #Array (Result varchar(8))

END

While len(@AccString) > 0

BEGIN

Set @Acc = left(@AccString,5) --Get first 5 chars

set @AccString = replace(@AccString,@Acc,'') --Remove each result from string

set @Acc = replace(@Acc,',','') --Remove comma from string

--Insert Data

Insert Into #Array

select @Acc

END

select * from #Array

Drop Table #Array

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You can also check out the DelimitedSplit function in this thread as well.

purplebirky SSC-Addicted Points: 461 More actions · Answer 4

the data will be diffrent, it's a list of menu choices. ie:

links, admin, user, database, books

and so on. no limit on how many. from what i understand one column is being used to store these choices, but they want to break them appart to display them as if they came out as diffrent records. I've already suggested just take what comes out and useing the frontend code to break it apart and they can do this, they just want to know if it's easier to do in sql.

[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

How about posting the table DDL, sample data, and expected results. Please read the first article referenced in my signature block about asking for help. Follow the instruction in that article to get the best tested help quickly.

AVB SSChampion Points: 11418 More actions · Answer 6

If the data is going to be different lengths then the code I provided would not work although it can be modified to do so.

As far as the function Lynn posted I believe that won't work for SQL 2000 which this topic is under (SQL 7,2000).

What version of SQL are you running?

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

AVB (8/4/2009)
If the data is going to be different lengths then the code I provided would not work although it can be modified to do so.
As far as the function Lynn posted I believe that won't work for SQL 2000 which this topic is under (SQL 7,2000).
What version of SQL are you running?

But the code in the following article will work... and it contains no explicit loops so it's also very fast.

http://www.sqlservercentral.com/articles/T-SQL/62867/

There's a subsection near the end of the article that shows how to very quickly and easily split an entire table's worth of CSV's in a single, very simple SELECT.

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

AVB SSChampion Points: 11418 More actions · Answer 8

Jeff,

Good article. I'll have to try that next opportunity I get!

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 9

AVB (8/4/2009)
Jeff,
Good article. I'll have to try that next opportunity I get!

Thanks for the feedback, AVB.

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

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 10

purplebirky (8/3/2009)
in this table one column has entrys that look like : res1, res2, res3
well i need to split these out to return each on it's own.

purplebirky,

See the article I posted above.

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