I found a handy-dandy little script that graz created a while back. All it does is splitting up a csv string. Here's my question: How could one do what he does there inside a UDF? Obviously, tables won't work inside of a UDF. Would it still be possible to split up a csv string and return a table?Reason I'm asking is--and maybe there's an entirely different solution available somewhere--that I need to pass into a sproc a csv string and need to be able to say:SELECT someValuesFROM someTableWHERE someValue IN (SELECT * FROM dbo.myFunction(@csvList))
Any ideas?Here is graz's script:Create procedure sp_ParseArray( @Array varchar(1000),@separator char(1) ) AS-- Created by graz@sqlteam.comset nocount on-- @Array is the array we wish to parse-- @Separator is the separator charactor such as a commadeclare @separator_position int -- This is used to locate each separator characterdeclare @array_value varchar(1000) -- this holds each array value as it is returned-- For my loop to work I need an extra separator at the end. I always look to the-- left of the separator character for each array valueset @array = @array + @separator-- Loop through the string searching for separtor characterswhile patindex('%' + @separator + '%' , @array) <> 0 begin -- patindex matches the a pattern against a string select @separator_position = patindex('%' + @separator + '%' , @array) select @array_value = left(@array, @separator_position - 1) -- This is where you process the values passed. -- Replace this select statement with your processing -- @array_value holds the value of this element of the array select Array_Value = @array_value -- This replaces what we just processed with and empty string select @array = stuff(@array, 1, @separator_position, '')endset nocount offgo
graz's query
Posted - 2002-07-18 : 11:23:23
