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

Could you please run the following queries:

SELECT  @@character_set_server, @@collation_server

SELECT  HEX(CAST(UPPER(address) AS binary)), HEX(CAST(address AS binary))
FROM    BasicJobInfo

Answer 2

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))

Answer 3

This might come from encoding issues. Performing case-insensitive comparisons is described in the appendix of the mysql help pages. Example for case-insensitive UTF8 column/string comparison:

SELECT *
FROM YourTable
WHERE address LIKE 'searchString' COLLATE utf8_general_ci;