Comma seperated list from subquery into split func

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 2000 Forums

Transact-SQL (2000)

Comma seperated list from subquery into split func

Author

Topic

sneiland
Starting Member

1 Post

Posted - 2008-11-11 : 13:31:00

Hi,
I have a table which stores a set of values as a comma seperated list.

myTable
PK List
1 a,b,c
2 d,f
3 f,t,c

I have written a function to split this list up, which returns a table of token and positions. dbo.mySplit(list,seperator)

I can call this by writing
SELECT * FROM dbo.mySplit('a,b,c',',') where I am explicitly hard coding the comma string into the call.
OR
I can also store the string in a variable and pass it in.

SELECT @list = list FROM myTable
SELECT * FROM dbo.mySplit(@list,',')

However what I would like to do is
SELECT * FROM dbo.mySplit(SELECT list from mytable,',')
where i am making a query within the parameters list. So far I keep getting message incorrect syntax.

Can somebody point out what I am doing wrong. It must be blindingly obvious but I cannot see what.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-11 : 13:46:42

I do not think you can do this or if you can, I do not know how. I think you would have to assign the List to a variable and then use that. Just like your example.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-11 : 14:30:48

Or, You could use a cursor to scan rows and store the result of your function in another table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 00:53:58

quote:
Originally posted by sneiland

Hi,
I have a table which stores a set of values as a comma seperated list.

myTable
PK List
1 a,b,c
2 d,f
3 f,t,c

I have written a function to split this list up, which returns a table of token and positions. dbo.mySplit(list,seperator)

I can call this by writing
SELECT * FROM dbo.mySplit('a,b,c',',') where I am explicitly hard coding the comma string into the call.
OR
I can also store the string in a variable and pass it in.

SELECT @list = list FROM myTable
SELECT * FROM dbo.mySplit(@list,',')

However what I would like to do is
SELECT * FROM dbo.mySplit(SELECT list from mytable,',')
where i am making a query within the parameters list. So far I keep getting message incorrect syntax.

Can somebody point out what I am doing wrong. It must be blindingly obvious but I cannot see what.

not sure about 2000. however if you're using sql 2005 you can use CROSS APPLY to achieve this

SELECT * from mytable t
CROSS APPLY dbo.mySplit(t.list,',')f

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources