[dba-SQLServer] A UDF to turn a list into a table

Hi Arthur:

Cool... Do you mind if I store that piece of code for later use.

Jim 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
artful at rogers.com
Sent: Tuesday, March 13, 2007 8:35 AM
To: dba-SQLServer
Subject: [dba-SQLServer] A UDF to turn a list into a table

I just dug up some code I wrote a long while back, because suddenly I needed
it. It's a function that parses a comma-delimited string and places the
contents into a table, then returns the table. 

Lots of front ends assemble a string and then pass it to a stored procedure
or function that then uses the string as the argument to an IN() clause.
This is woefully inefficient, and there is a better way. The following
function does what is described above. The cool thing is that you can join
the result table to some other table, with a significant performance gain
over the typical IN() approach.

Here is the function, followed by an example call:

<code>
CREATE FUNCTION dbo.StringToTable_fnt 
(
@String varchar(100)
)
RETURNS @Values TABLE (ID int primary key)
AS
BEGIN
DECLARE @pos int
DECLARE @value int
WHILE @string > ''
BEGIN
SET @pos = CHARINDEX(',', @string)
IF @pos > 0 
BEGIN
SET @value = SUBSTRING( @string, 1, @pos - 1)
SELECT @string = LTRIM(SUBSTRING( @string, @pos + 1, LEN(@string)- at pos+1))
INSERT @Values SELECT @value
END
ELSE
IF LEN(@string) > 0
BEGIN
SET @value = @string
INSERT @Values SELECT @value
SET @string = ''
END
ELSE
SET @string = ''
END
RETURN
END
-- SELECT * FROM dbo.StringToTable_fnt('100, 200, 300, 400, 500')
</code>

Don't mind the formatting. I pasted it directly from SSMS. You can clean it
up.

Hope this helps someone, 
Arthur Fuller
Technical Writer, Data Modeler, SQL Sensei
Artful Databases Organization
www.artfulsoftware.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com