Uppercase and Lowercase in MySQL query condition

Question

I am writing a SQL query which uses WHERE statement to find the record. The problem is MySQL seems not care about uppercase and lowercase string in the condition. My both bellow queries have same result.

SELECT * FROM mytable WHERE url = 'http://domain.com/aaa'

=> http://domain.com/aaa record (CORRECT)

SELECT * FROM mytable WHERE url = 'http://domain.com/AAA'

=> http://domain.com/aaa record (INCORRECT)

I searched on the Internet and tried to use BINARY and COLLATE utf8_bin but it didn't help. How can I make it work? Thank you in advance.

This http://alvinalexander.com/sql/sql-select-case-insensitive-query-queries-upper-lower may help shed some light on the subject. Also `Select Title from FooName where binary Title like '%foo%'` from http://technikhil.wordpress.com/2007/09/17/case-sensitive-matches-in-mysql/ — Funk Forty Niner, Aug 13 '14 at 15:12
or `WHERE BINARY url = '....'` if you can't/won't change the collation. — Marc B, Aug 13 '14 at 15:12
utf8_general_ci...the ci means case insensitive...so change the collation to one that is case sensitive. — Rudolfwm, Aug 13 '14 at 15:16
you tried `SELECT * FROM mytable WHERE url = 'http://domain.com/AAA' COLLATE utf8_bin` correct? — Rudolfwm, Aug 13 '14 at 15:19
Check this answer http://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive — xam, Aug 13 '14 at 15:30
@Rudolfwm I tried both `BINARY` and `COLLATE utf8_bin` but it does not work when I use the phpmyadmin querybox. Then I used the mysql command-line, and it work correctly. It is quite strange and I don't know why. — Ngo Duy Khanh, Aug 13 '14 at 15:43
@Ngo Duy Khanh : from what I remember, phpmyadmin adds collation automatically so that may override your query. Not sure. — Rudolfwm, Aug 13 '14 at 15:49

dev · Answer 1 · 2014-08-13T15:19:24.347

0

ok, you wants to have case sensitive comparison than you BINARY is the only best choice

SELECT * FROM mytable WHERE BINARY url = 'http://domain.com/aaa'

edited Aug 13 '14 at 15:19

answered Aug 13 '14 at 15:12

dev

439
2
6

score 0 · Answer 2 · answered Aug 13 '14 at 15:13

0

You can use the MySQL's built-in string functions

SELECT * FROM mytable WHERE url = LOWER('http://domain.com/AAA')

REF: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

answered Aug 13 '14 at 15:13

Edgar Ortega

1,672
18
22

score 0 · Accepted Answer · answered Aug 13 '14 at 15:14

Mysql query is not case sensitive by default but you can try binary operator to make it case sensitive. Here is the code which should work for you

For URL http://domain.com/aaa

SELECT * FROM table WHERE BINARY url = http://domain.com/aaa

For URL http://domain.com/AAA

SELECT * FROM table WHERE BINARY url = http://domain.com/AAA

I hope this helps you

Uppercase and Lowercase in MySQL query condition

3 Answers3