10

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.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
hackartist
  • 5,172
  • 4
  • 33
  • 48

2 Answers2

9

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

RocketDonkey
  • 36,383
  • 7
  • 80
  • 84
  • @mellamokb Well +1 to your comment for having proof to back it up :) – RocketDonkey Nov 12 '12 at 23:59
  • The cast won't make things run any slower will it? – hackartist Nov 13 '12 at 00:07
  • @hackartist Assuming you mean in comparison to your example above, you will see a slight slowdown simply due to the performance overhead of a function call (hopefully it won't be too significant in your situation). One interesting way to check is by using the `BENCHMARK` function to test the speed of execution. On my setup, if I run `SELECT BENCHMARK(10000000, CAST('abc' AS CHAR CHARACTER SET latin1) COLLATE latin1_general_ci)`, it takes ~1.2 seconds, while `SELECT BENCHMARK(10000000, 'abc')` takes ~.4. However reducing the number to 1,000,000 narrows the gap significantly. Good times :) – RocketDonkey Nov 13 '12 at 01:17
9

You can use the BINARY operator. Something like:

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

SQL Fiddle Demo

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164