CODEWITHSUNDEEP
javamysql
Jeremy
Jeremy

Reputation: 83

MYSQL empty value in column reguared as a wildcard

I have application where there would be several key columns, but some may be blank. I'm not sure how to select the data to match anything if the cell is empty, for example --

    Col1 | Col2 | Col3 | Col4
    ---------------------------
    red  | 100  | ABC  | RES1
    Blue |      | QWE  | RES2
         | 222  |      | RES3
    org  | 345  |      | RES4

Now I need to select rows like this ($xxx is a var, not language specific at the moment)-

SELECT * FROM tbl WHERE Col1=$var1 AND Col2=$var2 AND Col3=$var3

Then if I set var1=yel, var2=222, var3=KJH I need the result to be RES3

Or another example, var1=org, var2=345, var3=BNM I need the result to be RES4

But if I select va1=red, var2=333, var3=ABC I need 0 results

Basically the blanks in the rows need to be a wildcard that will match anything, This needs to be fast and I can only use one Select statement, Is this possible or do I have to rethink things? Thanks!

Upvotes: 0

Views: 44

Answers (2)

user6368519
user6368519

Reputation: 74

Assuming prepared statements (in Java) are used:

SELECT Col4
    FROM tbl
    WHERE (Col1=? OR Col1 IS NULL)
        AND (Col2=? OR Col2 IS NULL)
        AND (Col3=? OR Col3 IS NULL);

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

check for is null in OR 

SELECT * 
FROM tbl 
WHERE (Col1=$var1 or col1 is null)  
AND ( Col2=$var2 or col2 is null)
AND ( Col3=$var3 or col3 is null)

(is not in a specific language )

Upvotes: 0

Related Questions