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

[Frank11]

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

[Infide]

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.

DECLARE @PassedList VARCHAR(5000)
DECLARE @ParsedList VARCHAR(5000)
DECLARE @ROW VARCHAR(5000)
set @passedlist = '2,5,10,6'
 
declare @ParsedInt int
declare @CompareInt int
 
 
 
DECLARE  C cursor  LOCAL FAST_FORWARD FOR
SELECT  ColumnX
    From TableTest
 
OPEN C
 
FETCH NEXT FROM C INTO @Row
 
WHILE @@Fetch_Status = 0 
BEGIN
 
SET @ParsedList = @PassedList
while len(@ParsedList) > 0
BEGIN
    IF charindex(',',@ParsedList) > 0
    BEGIN
    SET @ParsedInt = substring(@ParsedList,0,charindex(',',@ParsedList))
    SET @ParsedList = substring(@passedlist,charindex(',',@ParsedList) + 1, len(@ParsedList))
    END
    ELSE
    BEGIN
    SET @ParsedInt = @ParsedList
    SET @ParsedList = ''
    END
 
    WHILE LEN(@Row) > 0--compare every comma delimited value in row to the parsed int from the passed list
    BEGIN
        IF charindex(',',@Row) > 0
        BEGIN
        SET @CompareInt = substring(@Row,0,charindex(',',@Row))
        SET @Row = substring(@Row,charindex(',',@Row) + 1, len(@Row))
        END
        ELSE
        BEGIN
        SET @CompareInt = @Row
        SET @Row = ''
        END
 
        IF (@CompareInt = @ParsedInt)
        BEGIN
            --DO SOMETHING
            print 'Found a match'
        END
 
 
    END--end row parse row loop
END--end parse passedlist loop
 
 
 
FETCH NEXT FROM C INTO @Row
 
END
 
 

[Frank11]

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.

DECLARE @PassedList VARCHAR(5000)
DECLARE @ParsedList VARCHAR(5000)
DECLARE @ROW VARCHAR(5000)
set @passedlist = '2,5,10,6'
 
declare @ParsedInt int
declare @CompareInt int
 
 
 
DECLARE  C cursor  LOCAL FAST_FORWARD FOR
SELECT  ColumnX
    From TableTest
 
OPEN C
 
FETCH NEXT FROM C INTO @Row
 
WHILE @@Fetch_Status = 0 
BEGIN
 
SET @ParsedList = @PassedList
while len(@ParsedList) > 0
BEGIN
    IF charindex(',',@ParsedList) > 0
    BEGIN
    SET @ParsedInt = substring(@ParsedList,0,charindex(',',@ParsedList))
    SET @ParsedList = substring(@passedlist,charindex(',',@ParsedList) + 1, len(@ParsedList))
    END
    ELSE
    BEGIN
    SET @ParsedInt = @ParsedList
    SET @ParsedList = ''
    END
 
    WHILE LEN(@Row) > 0--compare every comma delimited value in row to the parsed int from the passed list
    BEGIN
        IF charindex(',',@Row) > 0
        BEGIN
        SET @CompareInt = substring(@Row,0,charindex(',',@Row))
        SET @Row = substring(@Row,charindex(',',@Row) + 1, len(@Row))
        END
        ELSE
        BEGIN
        SET @CompareInt = @Row
        SET @Row = ''
        END
 
        IF (@CompareInt = @ParsedInt)
        BEGIN
            --DO SOMETHING
            print 'Found a match'
        END
 
 
    END--end row parse row loop
END--end parse passedlist loop
 
 
 
FETCH NEXT FROM C INTO @Row
 
END
 
 

[Vidhura]

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