CODEWITHSUNDEEP
mysqlsqlcase-sensitivein-clausestring-search
hackartist
hackartist

Reputation: 5264

Making MySQL IN Clause Case Sensitive

Does anyone know how I can make an IN clause behave in a case sensitive manner? I have seen that COLLATE can be used with LIKE for string searching but I don't know if or how it can be used with IN. For example I want to do something like 

SELECT * FROM pages_table WHERE topic IN ('Food','NightLife','Drinks')

And I want it to return pages where the topic is 'Food' but not those where the topic is 'food' which is currently what happens on this query. Thanks.

Upvotes: 11

Views: 9112

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

You can use the BINARY operator. Something like:

SELECT *
FROM pages_table
WHERE CAST(topic AS BINARY) IN ('Food','NightLife','Drinks');

SQL Fiddle Demo

Upvotes: 10

RocketDonkey
RocketDonkey

Reputation: 37269

You can actually use it as you have likely seen in other examples:

SELECT *
FROM pages_table
WHERE CAST(topic AS CHAR CHARACTER SET latin1)
        COLLATE latin1_general_cs IN ('Food','NightLife','Drinks')

This changes the character set into one that supports case sensitivity and then collates the column (you may not have to do this depending on your own character encoding).

Upvotes: 9

Related Questions