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).

Advertisement

Answer