Binaryworld - UDF : ParseArray - return table from CSV ... [ SQL Server -> User Defined Function ]

Sometimes we may need to store values in one field using some sort of seperating character. For example if you want to store multiple values in one field instead of normalizing your table and creating a whole new table to store multiple records. This articles will show you how you can create a UDF which can return table from a string which is CSV (Comma seperated values) stored in one field. By doing this you reduce complexity of your system.

Here is the user defined function

Click here to copy the following block

--// example : select * from dbo.ParseArray('1|2|3|4','|') as tmp

CREATE FUNCTION ParseArray
(@Array varchar(8000),
@separator char)
RETURNS @ParsedArrays TABLE (row_counter int identity(1,1), array_value varchar(8000))
AS
BEGIN

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(8000) -- this holds each array value as it is returned

--create table #ParsedArrays (array_Value varchar(8000))

-- 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 value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while 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
insert @ParsedArrays(array_value) VALUES (@array_value)

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
RETURN
END
GO

--//Example

select * from dbo.ParseArray('1|2|3|4','|') as tmp

Output

array_value              
------------------------- 
1
2
3
4

(4 row(s) affected)