469,592 Members | 1,983 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Help with a query: returning rows from a coma delimited list of integers...

Hi, I'm rather new to writing queries, so please forgive me if this is a really simple thing to do.

I need to create a query that finds out which rows in the DB have ANY of the values that are in a given list. Only one column in each row of the DB has their values stored in a list.

Example:

Passed list = 2,5,10,6

ColumX data in database:
Row 1 = 3, 5, 21, 4
Row 2 = 1, 4, 7, 9
Row 3 = 2
Row 4 = 8, 10, 3, 6
Row 5 = 1, 22, 9, 4

Base on the passed list, the query should return the following rows:
Row 1 (because 5 was in the list)
Row 3 (because 2 was in the list)
Row 4 (because 10 and 6 was in the list)

Iím hoping threes some SQL function that works with lists and can compare the data sets of two lists to find matching values, but I donít know.

Can anyone help?
thanks
Jul 15 '07 #1
3 1533
Infide
28
Hi, I'm rather new to writing queries, so please forgive me if this is a really simple thing to do.

I need to create a query that finds out which rows in the DB have ANY of the values that are in a given list. Only one column in each row of the DB has their values stored in a list.

Example:

Passed list = 2,5,10,6

ColumX data in database:
Row 1 = 3, 5, 21, 4
Row 2 = 1, 4, 7, 9
Row 3 = 2
Row 4 = 8, 10, 3, 6
Row 5 = 1, 22, 9, 4

Base on the passed list, the query should return the following rows:
Row 1 (because 5 was in the list)
Row 3 (because 2 was in the list)
Row 4 (because 10 and 6 was in the list)

Iím hoping threes some SQL function that works with lists and can compare the data sets of two lists to find matching values, but I donít know.

Can anyone help?
thanks
You might get stuck with having to use a cursor.

This should get you started.
Expand|Select|Wrap|Line Numbers
  1. DECLARE @PassedList VARCHAR(5000)
  2. DECLARE @ParsedList VARCHAR(5000)
  3. DECLARE @ROW VARCHAR(5000)
  4. set @passedlist = '2,5,10,6'
  5.  
  6. declare @ParsedInt int
  7. declare @CompareInt int
  8.  
  9.  
  10.  
  11. DECLARE  C cursor  LOCAL FAST_FORWARD FOR
  12. SELECT  ColumnX
  13.     From TableTest
  14.  
  15. OPEN C
  16.  
  17. FETCH NEXT FROM C INTO @Row
  18.  
  19. WHILE @@Fetch_Status = 0 
  20. BEGIN
  21.  
  22. SET @ParsedList = @PassedList
  23. while len(@ParsedList) > 0
  24. BEGIN
  25.     IF charindex(',',@ParsedList) > 0
  26.     BEGIN
  27.     SET @ParsedInt = substring(@ParsedList,0,charindex(',',@ParsedList))
  28.     SET @ParsedList = substring(@passedlist,charindex(',',@ParsedList) + 1, len(@ParsedList))
  29.     END
  30.     ELSE
  31.     BEGIN
  32.     SET @ParsedInt = @ParsedList
  33.     SET @ParsedList = ''
  34.     END
  35.  
  36.     WHILE LEN(@Row) > 0--compare every comma delimited value in row to the parsed int from the passed list
  37.     BEGIN
  38.         IF charindex(',',@Row) > 0
  39.         BEGIN
  40.         SET @CompareInt = substring(@Row,0,charindex(',',@Row))
  41.         SET @Row = substring(@Row,charindex(',',@Row) + 1, len(@Row))
  42.         END
  43.         ELSE
  44.         BEGIN
  45.         SET @CompareInt = @Row
  46.         SET @Row = ''
  47.         END
  48.  
  49.         IF (@CompareInt = @ParsedInt)
  50.         BEGIN
  51.             --DO SOMETHING
  52.             print 'Found a match'
  53.         END
  54.  
  55.  
  56.     END--end row parse row loop
  57. END--end parse passedlist loop
  58.  
  59.  
  60.  
  61. FETCH NEXT FROM C INTO @Row
  62.  
  63. END
  64.  
  65.  
Jul 15 '07 #2
Infide

Thanks for the reply, but dam, I've never seen SQL code like that. Looks more like a program in C than a query.
I copy and pasted what you wrote into my query (its being sent within a Cold Fusion query) and the system doesn't like it. No error, but the query name that was given in the CF tag doens't is not available for the Output.

Would there be a simpler way you think to compare the values of these to lists?

cheers
F.






You might get stuck with having to use a cursor.

This should get you started.
Expand|Select|Wrap|Line Numbers
  1. DECLARE @PassedList VARCHAR(5000)
  2. DECLARE @ParsedList VARCHAR(5000)
  3. DECLARE @ROW VARCHAR(5000)
  4. set @passedlist = '2,5,10,6'
  5.  
  6. declare @ParsedInt int
  7. declare @CompareInt int
  8.  
  9.  
  10.  
  11. DECLARE  C cursor  LOCAL FAST_FORWARD FOR
  12. SELECT  ColumnX
  13.     From TableTest
  14.  
  15. OPEN C
  16.  
  17. FETCH NEXT FROM C INTO @Row
  18.  
  19. WHILE @@Fetch_Status = 0 
  20. BEGIN
  21.  
  22. SET @ParsedList = @PassedList
  23. while len(@ParsedList) > 0
  24. BEGIN
  25.     IF charindex(',',@ParsedList) > 0
  26.     BEGIN
  27.     SET @ParsedInt = substring(@ParsedList,0,charindex(',',@ParsedList))
  28.     SET @ParsedList = substring(@passedlist,charindex(',',@ParsedList) + 1, len(@ParsedList))
  29.     END
  30.     ELSE
  31.     BEGIN
  32.     SET @ParsedInt = @ParsedList
  33.     SET @ParsedList = ''
  34.     END
  35.  
  36.     WHILE LEN(@Row) > 0--compare every comma delimited value in row to the parsed int from the passed list
  37.     BEGIN
  38.         IF charindex(',',@Row) > 0
  39.         BEGIN
  40.         SET @CompareInt = substring(@Row,0,charindex(',',@Row))
  41.         SET @Row = substring(@Row,charindex(',',@Row) + 1, len(@Row))
  42.         END
  43.         ELSE
  44.         BEGIN
  45.         SET @CompareInt = @Row
  46.         SET @Row = ''
  47.         END
  48.  
  49.         IF (@CompareInt = @ParsedInt)
  50.         BEGIN
  51.             --DO SOMETHING
  52.             print 'Found a match'
  53.         END
  54.  
  55.  
  56.     END--end row parse row loop
  57. END--end parse passedlist loop
  58.  
  59.  
  60.  
  61. FETCH NEXT FROM C INTO @Row
  62.  
  63. END
  64.  
  65.  
Jul 15 '07 #3
Vidhura
99
Infide

Thanks for the reply, but dam, I've never seen SQL code like that. Looks more like a program in C than a query.
I copy and pasted what you wrote into my query (its being sent within a Cold Fusion query) and the system doesn't like it. No error, but the query name that was given in the CF tag doens't is not available for the Output.

Would there be a simpler way you think to compare the values of these to lists?

cheers
F.
Refer this

http://weblogs.sqlteam.com/dinakar/a.../28/60150.aspx
Jul 16 '07 #4

Post your reply

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

Similar topics

3 posts views Thread by Funnyweb | last post: by
3 posts views Thread by waters | last post: by
8 posts views Thread by skumar434 | last post: by
4 posts views Thread by Hemant Shah | last post: by
1 post views Thread by trint | last post: by
1 post views Thread by vikjohn | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.