You want to convert a string from one character set or collation to another.

Use the CONVERT() function to convert a string to another character set. Use the COLLATE operator to convert a string to another collation.

To convert a string from one character set to another, use the CONVERT() function:

mysql>SET @s1 = 'my string';
mysql> SET @s2 = CONVERT(@s1 USING utf8);
mysql> SELECT CHARSET(@s1), CHARSET(@s2);
+--------------+--------------+
| CHARSET(@s1) | CHARSET(@s2) |
+--------------+--------------+
| latin1       | utf8         |
+--------------+--------------+

To change the collation of a string, use the COLLATE operator:

mysql>SET @s1 = 'my string';
mysql> SET @s2 = @s1 COLLATE latin1_spanish_ci;
mysql> SELECT COLLATION(@s1), COLLATION(@s2);
+-------------------+-------------------+
| COLLATION(@s1)    | COLLATION(@s2)    |
+-------------------+-------------------+
| latin1_swedish_ci | latin1_spanish_ci |
+-------------------+-------------------+

The new collation must be legal for the character set of the string. For example, you can use the utf8_general_ci collation with utf8 strings, but not with latin1 strings:

mysql>SELECT _latin1 'abc' COLLATE utf8_bin;
ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for
CHARACTER SET 'latin1'

To convert both the character set and collation of a string, use CONVERT() to change the character set, and apply the COLLATE operator to the result:

mysql>SET @s1 = 'my string';
mysql> SET @s2 = CONVERT(@s1 USING utf8) COLLATE utf8_spanish_ci;
mysql> SELECT CHARSET(@s1), COLLATION(@s1), CHARSET(@s2), COLLATION(@s2);
+--------------+-------------------+--------------+-----------------+
| CHARSET(@s1) | COLLATION(@s1)    | CHARSET(@s2) | COLLATION(@s2)  |
+--------------+-------------------+--------------+-----------------+
| latin1       | latin1_swedish_ci | utf8         | utf8_spanish_ci |
+--------------+-------------------+--------------+-----------------+

The CONVERT() function can also be used to convert binary strings to nonbinary strings and vice versa. To produce a binary string, use binary; any other character set name produces a nonbinary string:

mysql>SET @s1 = 'my string';
mysql> SET @s2 = CONVERT(@s1 USING binary);
mysql> SET @s3 = CONVERT(@s2 USING utf8);
mysql> SELECT CHARSET(@s1), CHARSET(@s2), CHARSET(@s3);
+--------------+--------------+--------------+
| CHARSET(@s1) | CHARSET(@s2) | CHARSET(@s3) |
+--------------+--------------+--------------+
| latin1       | binary       | utf8         |
+--------------+--------------+--------------+

Alternatively, you can produce binary strings using the BINARY operator, which is equivalent to CONVERT( str USING binary):

mysql>SET @s1 = 'my string';
mysql> SET @s2 = BINARY @s2;
mysql> SELECT CHARSET(@s1), CHARSET(@s2);
+--------------+--------------+
| CHARSET(@s1) | CHARSET(@s2) |
+--------------+--------------+
| latin1       | binary       |
+--------------+--------------+