Compare comma separated list with individual row in table

Question

I have to compare comma separated values with a column in the table and find out which values are not in database. [kind of master data validation]. Please have a look at the sample data below:

table data in database:

id   name
1    abc
2    def
3    ghi

SQL part :

Here i am getting comma separated list like ('abc','def','ghi','xyz'). now xyz is invalid value, so i want to take that value and return it as output saying "invalid value".

It is possible if i split those value, take it in temp table, loop through each value and compare one by one.

but is there any other optimal way to do this ??

I am already using split function to separate the values. but do i need to check individual value or i can check it in single query ? — Jony shah, Jul 06 '14 at 00:19
possible duplicate of [Parameterize a SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause) — Clockwork-Muse, Jul 06 '14 at 04:07

score 0 · Answer 1 · answered Jul 06 '14 at 00:23

I'm sure if I got the question right, however, I would personally be trying to get to something like this:

SELECT
    D.id,
    CASE 
        WHEN B.Name IS NULL THEN D.name
        ELSE "invalid value"
    END
FROM
    data AS D
    INNER JOIN badNames B   ON  b.Name = d.Name
    --as SQL is case insensitive, equal sign should work

There is one table with bad names or invalid values if You prefer. This can a temporary table as well - depending on usage (a black-listed words should be a table, ad hoc invalid values provided by a service should be temp table, etc.).

NOTE: The select above can be nested in a view, so the data remain as they were, yet you gain the correctness information. Otherwise I would create a cursor inside a function that would go through the select like the one above and alter the original data, if that is the goal...

Solomon Rutzky · Answer 2 · 2014-07-06T03:36:55.100

It sounds like you just need a NOT EXISTS / LEFT JOIN, as in:

SELECT tmp.InvalidValue
FROM   dbo.HopeThisIsNotAWhileBasedSplit(@CSVlist) tmp
WHERE  NOT EXISTS (
                    SELECT *
                    FROM   dbo.Table tbl
                    WHERE  tbl.Field = tmp.InvalidValue
                  );

Of course, depending on the size of the CSV list coming in, the number of rows in the table you are checking, and the style of splitter you are using, it might be better to dump the CSV to a temp table first (as you mentioned doing in the question).

score 0 · Answer 3 · answered Jul 06 '14 at 03:55

0

Try following query:

SELECT  SplitedValues.name, 
        CASE WHEN YourTable.Id IS NULL THEN 'invalid value' ELSE NULL END AS Result
FROM SplitedValues 
LEFT JOIN yourTable ON SplitedValues.name = YourTable.name

answered Jul 06 '14 at 03:55

mehdi lotfi

11,194
18
82
128

Compare comma separated list with individual row in table

3 Answers3