How to interpret a column as having a different character set per query?

I need to interface with a database for which I cannot change the collation and charset. However, I would like to pick some binary data from it, interpret it as if it were UTF8 and then do an UPPER on it (since just doing UPPER() on binary returns the raw value).

I would assume that this w开发者_C百科orks:

SELECT UPPER(Filename.Name) COLLATE utf8_general_ci FROM Filename;

but it doesn't and complains that

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'

which is fair enough, I need some incantation to cast the binary field as being utf-8. How do I do a select which gives me a computed column with the right character set assigned to it?

Ok figured it out. For modern MySQL versions you can use CAST, and for older ones CONVERT (which is actually standard SQL).

 SELECT UPPER(CONVERT(BINARY(Filename.Name) USING utf8)) FROM Filename;

I think you're looking for:

SELECT UPPER(Filename.Name COLLATE utf8_general_ci) FROM Filename;

But I'm not sure because I don't have a broken database to test with.