How to parse a comma separated string into a SQL table, Part 2

In an earlier article we showed how to turn a comma separated list into a table.

I've been using that code for some time, especially when I needed to turn a multi value parameter from SSRS report into something that I can use.

Having done that a few times, it occurred to me that it might be easier to change it into a table value function - now there is no editing, I just include the function in my code and and we're off to the races.

In other words, SSRS gives you something like "apple, banana, pear"

We call the function like this:

select rs.*
    from myReportSource rs
        join dbo.f_Dyn_CommaSepListToTable('apple, bananna, pear') f on f.vchr100 = rs.fruitName

And all the work is done for us.

In the code above, 'apple, bannana, pear' come to as as the multi select parameter in SSRS. The function turns this into a table; that table is joined to the main report source and the report source is correctly filtered.

 

Related Articles

... and you 'll find more on the SQL (General) Menu

 

IF OBJECT_ID (N'dbo.ddf_CommaSepListToTable') IS NOT NULL
   DROP FUNCTION dbo.ddf_CommaSepListToTable
GO
  
  
CREATE FUNCTION dbo.ddf_CommaSepListToTable(@MyCommaSeparatedParam varchar(Max)) 
  
RETURNS @myCustomTable TABLE (vchr100 varchar(100) )
AS
  
--select * from ddf_CommaSepListToTable('asd,qwer,1234')
  
  
begin
    declare @singleParamValue varchar(15) 
    declare @pos int
  
    --find out where the first comma is
    set @pos = CHARINDEX(',',@MyCommaSeparatedParam) 
          
    while @pos > 0 begin
        --get the first value
        set @singleParamValue = RTRIM(ltrim(LEFT(@MyCommaSeparatedParam,@pos-1)))  
  
        --insert the value into our custom table
        insert into @MyCustomTable(vchr100) values (@singleParamValue) 
          
        --get rid of the value that we just brought in
        set @MyCommaSeparatedParam = RTRIM(ltrim(SUBSTRING(@MyCommaSeparatedParam,@pos + 1 , 100) ))  
          
        --find out where the next comma is
        set @pos = CHARINDEX(',',@MyCommaSeparatedParam) 
    end
  
    --insert the last value into the table
    insert into @MyCustomTable(vchr100) values (@MyCommaSeparatedParam) 
  
     
  
    RETURN
end
  
  
  
GO

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences