Toshi Toshi - 13 days ago 8
SQL Question

SQL Case insensitive IN search

I have this Table

"Table"
with content:

+--------+
| Serial |
+--------+
| d100m | <- expected result
| D100M | <- expected result
| d200m | <- expected result
| d300L |
| D400R |
+--------+


There are case inaccurate serial numbers stored.

Currently I am selecting there with a statement like

SELECT Serial FROM Table WHERE Serial LIKE 'D100M' OR Serial LIKE 'D200M';


But isn't there a easier way instead of
OR Serial LIKE OR Serial LIKE OR Serial LIKE
there are alomst 30 numbers i have to compare with.

Something like this

SELECT Serial FROM Table WHERE Serial LIKE IN ('D100M', 'D200M')

Answer

The easiest way would be:

SELECT Serial 
FROM Table 
WHERE upper(Serial) in ('D100M', 'D200M');

That won't however use an index on the serial column.

So if performance is a concern you would need to create an index on upper(serial).