Home > MSSQL, SQL > Converting a List to table

Converting a List to table

This function accepts a String which holds list of id’s and returns a table and then you can use this table to join and filter main table data. You can pass the list with your choice of separator.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create function [dbo].[ListToTable]
( @list varchar(4000), @separator varchar(10) )

/* this function receives a @separator delimited list and outputs a table with
each item in the list as a row in a table so the following can be used
SELECT * from dbo.Customers
inner join listToTable( ‘123,456,789’, ‘,’ )
on CustomerID = listValue
*/

returns @listTable table( listValue Int null )
as
begin

if @list is null
insert into @listTable values( null )

— @list is the list 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 @list_value varchar(1000) — this holds each list 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 list value
set @list = @list + @separator

— Loop through the string searching for separator characters
while patindex(‘%’ + @separator + ‘%’ , @list) 0
begin

— patindex matches the a pattern against a string
select @separator_position = patindex(‘%’ + @separator + ‘%’ , @list)
select @list_value = left(@list, @separator_position – 1)

insert into @listTable values( @list_value )

— This replaces what we just processed with and empty string
select @list = stuff(@list, 1, @separator_position, ”)
end

return

end

Advertisements
Categories: MSSQL, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: