SQL Case insensitive IN search

I have this Table "Table" with content:

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

​x
 
+--------+| 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';

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

 
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');

 
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