[RESOLVED] function "upper" doesn't work on BLOB?

[dewen]

I have a MySQL 5.0.21.

two fields in table:

headline            | varchar(100)
description         | blob 

Follow query used to work in 3.23

select upper(description), upper(headline) from rental where id=1101;

and print both fields in uppercase. But after I upgrade to MySQL 5, upper(description) doesn't give uppercase anymore.

Any help appreciated!

[MarkR]

A BLOB contains binary data. MySQL doesn't have a clue what that binary data contains, therefore the upper() function makes no sense.

upper() only makes sense on text data, and a BLOB isn't that.

If previous versions allowed it, they were making a (possibly incorrect) about the type of data the BLOB contained- therefore it was a bug which has since been fixed.

A BLOB does not have a character encoding (it can contain arbitrary binary data), therefore there is no fixed algorithm to convert it to upper case, even if it happened to contain text in some encoding.

Mark

[bradgrafelman]

In other words, you might want to redesign your database here. Does "description" ever contain anything other than text? If not, I would use a TEXT type instead of a BLOB type.

[dewen]

Thanks, it's a great clue. I'm going to do some research and see the best solution to it.