Case insensitive search (and order by case insensitively) in a case-sensitive column

Question

I have a case-sensitive colume(utf8_bin collation). I need to search a string case-insensitively and order the results case-insensitively.

Is have written this query.

SELECT customer_name 
   FROM customers
   WHERE CONVERT(customer_name USING UTF8) LIKE 'aB%' 
   ORDER BY CONVERT(customer_name USING UTF8) 
   LIMIT 0,10

Is this efficient? Or is there a better way to achieve this?

score 3 · Answer 1 · answered Apr 24 '13 at 11:35

3

What about converting in uppercase?

SELECT customer_name 
   FROM customers
   WHERE UPPER(customer_name) LIKE 'AB%' 
   ORDER BY UPPER(customer_name) 
   LIMIT 0,10

answered Apr 24 '13 at 11:35

TheEwook

11,037
6
36
55

1

Yeah. I had that initially. But I saw [here](http://stackoverflow.com/a/2876802/1137788) that this affects the use of Index. Hence I used Collate. – SatheeshJM Apr 24 '13 at 11:42
Thanks for sharing the link. I didn't know Collate ;) – TheEwook Apr 24 '13 at 11:47

Case insensitive search (and order by case insensitively) in a case-sensitive column

1 Answers1