VB and VBA Users Source Code: SQL Tip - Converting a deliminated list into a temporary table
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
SQL Tip - Converting a deliminated list into a temporary table
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Thursday, May 08, 2003
Hits:
957
Category:
Database/SQL/ADO
Article:
The code below shows how to convert a deliminated list into a temporary table. This is very useful when passing lists into stored procedures. --Note: Replace len with char_length for Sybase --Converts a deliminated list to a temporary table set nocount on declare @delim_list varchar(255),@delim char(1) --Deliminated list of names select @delim_list = "andrew,bob,peter,john" --Delimiter select @delim = "," --Create temp table create table #params(param varchar(20)) --Adjust this to suit --Check that last character is a delimeter if substring(@delim_list,len(@delim_list),1) != @delim select @delim_list = @delim_list + @delim while (len(@delim_list) > 0) begin --Insert parameter into temp table insert #params values(substring(@delim_list, 1,charindex(@delim, @delim_list) -1)) --Remove parameter from list select @delim_list = substring(@delim_list, charindex(@delim, @delim_list) + 1, len(@delim_list) - charindex(@delim, @delim_list)) end --Display results select * from #params --Insert code which joins with temp. table here --Drop table drop table #params
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
