We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

MySQL Case Sensitivity with BINARY VALUES

phipsta
phipsta asked
on
Medium Priority
590 Views
Last Modified: 2012-05-06
I have some binary data that i want to convert to lower or upper case to compare to a string.

SELECT myBinaryData FROM table_a WHERE myBinaryData Like "UPPERCASE";

The string in the where clause will be mixed case as will the binary data, i would like to know if it is possible to convert them both to either lower or uppercase to make the comparison.

LCASE() or UPPER() does not seem to work with binary data values, is there any other solution to this? (No points for telling me to change my data type to non binary)
Comment
Watch Question

CERTIFIED EXPERT
Commented:
See the use of the CONVERT function: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

maybe you can do something like:

SELECT myBinaryData FROM table_a WHERE UPPER(CAST(myBinaryData AS CHAR)) LIKE "UPPERCASE";

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Your example given works like a dream. In fact you now don't need to convert the column to upper case as now both and treated like CHAr data types and the LIKE statement is not case sensitive.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.