Split Value where "," into rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split Value where "," into rows

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-01-16 : 07:08:35

Good day, please help

I have a value "Test 01, Test 02, Test 03"

I would like to return in rows:
Test 01
Test 02
Test 03

Please help

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 07:12:59

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-01-16 : 07:23:07

Hi, thanks for the help.

Guess i have to perhaps create a table as that is to hectic for me.

I thought it would be sql i understand, as i actually do the query within a vbscript window.

So the part of create & delcaring temp tables/var is not an option for me.

I would have to think of another way :(

Regards

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 08:13:30

You don't have to declare any temp tables (so far as I know), you can just do


SELECT ... somecolumns ...
FROM dbo.MySplitFunction('Test 01, Test 02, Test 03')

If the comma delimited value is actually in an existing column in a database table then you can do something similar (but slightly more complicated)

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-01-16 : 08:36:32

Thanks
Do i create the function on my db.
Does the function have a parameter that i can pass a value from another select.

Please Help:
1. Post MySplitFunction for me to create on DB with Param
2. How do i use the function passing Param value to function from query?

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-16 : 08:57:25

All your questions are answered in Kristen's first post (link).
There you can see more than one approach to create a splitting function in SQL Server database.
And here you can get an idea on how to use a function in a select statemant.
http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 09:00:14

"Do i create the function on my db."

yes

"Does the function have a parameter that i can pass a value from another select."

You need a different function for that, but basically "yes"

The answers to your question (1) and (2) should be in the link I posted. Sorry its a long thread, but there are issues that you need to consider, depending on what you want to do and what type of data etc. you have.

I suggest you start at the end of the thread and work back - that will give you the most recent solutions first. The early messages in the thread will relate to SQL 2000 version and earlier.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-01-16 : 09:17:56

Thank You, I got it going using the below :)
Need help:
1. How do i replace the first param with a result from a query
2. Need to get the value of the first record returned
in the result "7"
It has to be added to the below query!

Select * from dbo.Split('7,5,11',',')
ID Value
1 7
2 5

--CREATE FUNCTION dbo.Split
--(
-- @RowData nvarchar(2000),
-- @SplitOn nvarchar(5)
--)
--RETURNS @RtnValue table
--(
-- Id int identity(1,1),
-- Data nvarchar(100)
--)
--AS
--BEGIN
-- Declare @Cnt int
-- Set @Cnt = 1
--
-- While (Charindex(@SplitOn,@RowData)>0)
-- Begin
-- Insert Into @RtnValue (data)
-- Select
-- Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
--
-- Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
-- Set @Cnt = @Cnt + 1
-- End
--
-- Insert Into @RtnValue (data)
-- Select Data = ltrim(rtrim(@RowData))
--
-- Return
--END

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-01-16 : 11:00:05

Hi,

how do i alter the function to read from select query results?

Regards

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-01-16 : 11:57:50

Thank You All

Great stuff - got it going :)

declare @str varchar(100)
set @str = (select VendorRequestUser from dbo.fcUser where firstname = 'ismail')
select * from dbo.split(@str,',')

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 12:34:18

Yes, that will work fine if you only have one row in your fcUser table that you need to "split"

Glad you have got it working

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 02:36:06

Note that this can be very easily done in front end application using a built-in split function
If you use front end application, do splitting there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources