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.

Accepted Answer

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

Why does UPPER() not work in MySQL?

Solution

Problem