DelphiFAQ Home Search:

How to compare strings in mysql case sensitive

 

comments9 comments. Current rating: 5 stars (2 votes). Leave comments and/ or rate it.

Question:

My application includes a query where I want to list all products with a name that starts in upper case. The SQL looked like

.. where prod_name >= 'A' and prod_name <= 'Z';

Turns out this included lowercase products. A further investigation shows

select 'A' = 'a';

returns TRUE (1). How can I make mysql case sensitive?

Answer:

Instead of using the = operator, you may want to use LIKE or LIKE BINARY

// this returns 1 (true)
select 'A' like 'a'

// this returns 0 (false)
select 'A' like binary 'a'

Content-type: text/html

Comments:

2008-06-07, 21:05:17
anonymous from United States  
rating
Nice and simple article. I was looking for a way to do this that was simple, and you're shown just that. Keep it up!!
2008-06-17, 05:18:39
anonymous from France  
Found this useful until I realized that some of the strings I want to compare contain '_' and '%' characters that LIKE will interpret as wildcards.

I then tried SELECT BINARY A = B, that solved my problem.

2009-06-29, 05:13:51
anonymous from Portugal  
Well Done thats it.
2009-08-12, 05:31:40
anonymous  
rating
Thank u gr8 work man!!!!!
2009-08-24, 09:45:23   (updated: 2009-08-24, 09:46:07)
anonymous from Canada  
Thank u gr8 - I had a login that was redirecting to another directory and the case had to be the same as it.
2009-12-22, 06:13:57
anonymous from Indonesia  
wonderful trick, thanks.
2010-03-26, 07:36:59
happy-feet from India  
thanks a lot !!
it helped :))
2011-11-20, 11:03:00
Black  
Thank you! After 2 years, this helped me much.

Greetings
2012-12-30, 08:01:24
anonymous from Germany  
Thank you!even after 3 years, this helped me much.

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: