Making MySQL IN Clause Case Sensitive

mysql sql case-sensitive in-clause string-search
hackartist picture hackartist · Nov 13, 2012 · Viewed 7.9k times · Source

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.

Answer

RocketDonkey picture RocketDonkey · Nov 13, 2012

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

Related questions

How can I make SQL case sensitive string comparison on MySQL?

I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case. How can I make MySQL string queries case sensitive?

Read More
How do you force mysql LIKE to be case sensitive?

Possible Duplicate: Mysql Like Case Sensitive Mysql ignores case for its LIKE comparisons. How can you force it to perform case-sensitive LIKE comparisons?

Read More
SQL unique varchar case sensitivity question

I'm trying to populate a SQL table with a list of words. The table itself it pretty simple: CREATE TABLE WORDS( ID BIGINT AUTO_INCREMENT, WORD VARCHAR(128) NOT NULL UNIQUE, PRIMARY KEY(ID) ); The problem I'm running into is this: …

Read More