Why does UPPER() not work in MySQL?

Question

My SQL query selects some columns from from a view with an optional sort field. Basically, my view concatenates a number of fields into a single address string, so that I end up with something like

123 Sesame Street Birdtown

in the address column. I want the search to be case-insensitive (it isn't by default), so I tried this:

SELECT * FROM BasicJobInfo WHERE UPPER(address) LIKE UPPER(searchString)

with searchString being the address that I want to find. However, MySQL seems to be unable to convert address to UpperCase - I tried just calling

SELECT UPPER(address) FROM BasicJobInfo

but it doesn't change the case at all. Any thoughts as to why this might be?
Also, any suggestions as to how else I can do a case-insensitive search?
Many thanks.

Answer 1

According to the MySQL Reference Manual page on String Functions:

UPPER() is ineffective when applied to binary strings (BINARY, VARBINARY, BLOB).

Is it possible your ADDRESS column has a BINARY, VARBINARY, or BLOB data type?

If it is you will need to convert the Binary String to an "ordinary" string. For example:

UPPER(CONVERT(ADDRESS USING latin1))