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

It's very similar to mine :)

http://sqlthis.blogspot.com/2005/02/list-to-table.html

On 3/13/07, artful at rogers.com <artful at rogers.com> wrote:
> 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
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...