CODEWITHSUNDEEP
mysql
rotsner
rotsner

Reputation: 692

MYSQL IN statement where values are case-insensitive

I am currently trying to whitelist/blacklist based on user input with the following SQL statements:

SELECT * FROM Customers WHERE City IN ('Paris','London');
SELECT * FROM Customers WHERE City NOT IN ('Paris','London');

What syntax is needed such that Paris and London are case insensitive and match paris and london?

Thanks.

Upvotes: 0

Views: 71

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Just define the proper collation on your db fields and index will work fine too.

http://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html

As you can see with latin1_bin 'MySQL' <> 'mysql'

mysql> SET @s1 = 'MySQL' COLLATE latin1_bin,
    ->     @s2 = 'mysql' COLLATE latin1_bin;
mysql> SELECT @s1 = @s2;
+-----------+
| @s1 = @s2 |
+-----------+
|         0 |
+-----------+

But with latin1_swedish_ci are equal

mysql> SELECT @s1 COLLATE latin1_swedish_ci = @s2;
+-------------------------------------+
| @s1 COLLATE latin1_swedish_ci = @s2 |
+-------------------------------------+
|                                   1 |
+-------------------------------------+

Upvotes: 3

Ed Gibbs
Ed Gibbs

Reputation: 26343

You can do this, but if you've indexed City hoping to optimize the query, adding the LOWER function will kill the optimization:

SELECT * FROM Customers WHERE LOWER(City) IN ('paris', 'london');

Upvotes: 2

Related Questions