469,344 Members | 6,305 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

How to Pass Comma-Separated String into Query

124 100+
I wrote a function that takes a comma-separated string, parses out the individual values, places quotations around each value and a comma in-between each value so that they can be processed in a query. This is my function:

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION dbo.fnParseCSV
  2. (
  3. @ExcludedCourses varchar(255)
  4. )
  5. RETURNS varchar(255)
  6. AS
  7. BEGIN
  8.     DECLARE @ParsedExcludedCourses varchar(255)
  9.     DECLARE @ExcludedCourse varchar(10)
  10.     DECLARE @Pos int
  11.  
  12.     WHILE LEN(@ExcludedCourses) > 0
  13.     BEGIN
  14.         SET @Pos = CHARINDEX(',',@ExcludedCourses)
  15.  
  16.         IF @Pos = 0
  17.             BEGIN
  18.                 SET @ParsedExcludedCourses = ISNULL(@ParsedExcludedCourses,'') + '''' + LTRIM(@ExcludedCourses) + ''''
  19.             END
  20.         ELSE
  21.             BEGIN
  22.                 SET @ExcludedCourse = '''' + SUBSTRING(@ExcludedCourses, 1, @Pos - 1) + ''''
  23.                 SET    @ParsedExcludedCourses = @ExcludedCourse + ',' + ISNULL(@ParsedExcludedCourses,'')
  24.             END
  25.  
  26.         IF @Pos = 0
  27.             BEGIN
  28.                 SET @ExcludedCourses = ''
  29.             END
  30.         ELSE
  31.             BEGIN
  32.                 SET @ExcludedCourses = LTRIM(SUBSTRING(@ExcludedCourses, @Pos + 1, LEN(@ExcludedCourses) - @Pos))
  33.             END
  34.     END
  35.  
  36. RETURN @ParsedExcludedCourses
  37. END
  38. GO
  39.  
This is how it works when utilized:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @courses varchar(255)
  2. SET @courses = 'COL 100,COM 100,MAT 100'
  3.  
  4. SELECT dbo.fnParseCSV(@courses)
  5.  
  6. Results: 'COL 100','COM 100','MAT 100'
  7.  
However, if I try executing the following query using the function I get zero results:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @courses varchar(255)
  2. SET @courses = 'COL 100,COM 100,MAT 100'
  3.  
  4. SET @courses = dbo.fnParseCSV(@courses)
  5.  
  6. SELECT *
  7. FROM Courses
  8. WHERE CourseId IN (@courses)
  9.  
But if I execute the query as such I get results:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Courses
  3. WHERE CourseId IN ('COL 100','COM 100','MAT 100')
  4.  
I am at a loss as to why it's not working. Any help is much appreciated!
Aug 10 '10 #1

✓ answered by gpl

You have to return a table value -- here is an example for returning a set of ints
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION dbo.CsvToTable (@Array VARCHAR(1000))
  2. RETURNS @IntTable TABLE (IntValue int)
  3. AS
  4. BEGIN
  5.     DECLARE @Separator Char(1)
  6.     DECLARE @SeparatorPos int
  7.     DECLARE @ArrayValue Varchar(1000)
  8.  
  9.     SET @Separator = ','
  10.     SET @Array = @Array + ','
  11.  
  12.     WHILE PATINDEX('%,%', @Array) <> 0
  13.     BEGIN
  14.         SET @SeparatorPos = PATINDEX('%,%', @Array)
  15.         SET @ArrayValue = LEFT(@Array, @SeparatorPos -1)
  16.  
  17.         INSERT @IntTable VALUES(
  18.             CAST(@ArrayValue AS INT))
  19.  
  20.         SET @Array = STUFF(@Array, 1, @SeparatorPos, '')
  21.     END
  22.  
  23.     RETURN
  24. END
  25.  

4 5082
gpl
152 100+
You have to return a table value -- here is an example for returning a set of ints
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION dbo.CsvToTable (@Array VARCHAR(1000))
  2. RETURNS @IntTable TABLE (IntValue int)
  3. AS
  4. BEGIN
  5.     DECLARE @Separator Char(1)
  6.     DECLARE @SeparatorPos int
  7.     DECLARE @ArrayValue Varchar(1000)
  8.  
  9.     SET @Separator = ','
  10.     SET @Array = @Array + ','
  11.  
  12.     WHILE PATINDEX('%,%', @Array) <> 0
  13.     BEGIN
  14.         SET @SeparatorPos = PATINDEX('%,%', @Array)
  15.         SET @ArrayValue = LEFT(@Array, @SeparatorPos -1)
  16.  
  17.         INSERT @IntTable VALUES(
  18.             CAST(@ArrayValue AS INT))
  19.  
  20.         SET @Array = STUFF(@Array, 1, @SeparatorPos, '')
  21.     END
  22.  
  23.     RETURN
  24. END
  25.  
Aug 10 '10 #2
NeoPa
32,183 Expert Mod 16PB
As gpl says, the IN() structure takes either a list of items, or a table/cursor. To help understand, what you are trying to do is equivalent to :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [States]
  3. WHERE  [StateAbb] IN('''IN'',''OH'',''NY''')
but you are really looking for a last line of :
Expand|Select|Wrap|Line Numbers
  1. WHERE  [StateAbb] IN('IN','OH','NY')
Multiple (list of) strings rather than a string formulated to contain strings.

Does that clarify?
Aug 11 '10 #3
bullfrog83
124 100+
OK, I understand now. The table function works very well and I like it better than what I had.
Aug 11 '10 #4
NeoPa
32,183 Expert Mod 16PB
Glad that helped :)

I agree about the table thingy too. I'm just getting into SQL Server myself from many years in Access. This is very much a server style thing (returning tables etc) and something I have to consider more going forward. I'm enjoying the extra scope and learning about capabilities just not available to Access work.
Aug 11 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Morris | last post: by
4 posts views Thread by newsprofile | last post: by
7 posts views Thread by MattyWix | last post: by
13 posts views Thread by artev | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.