Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Character Set and Collation of Function Results

12.5.3 Character Set and Collation of Function Results

MySQL has many operators and functions that return a string. This section answers the question: What is the character set and collation of such a string?

For simple functions that take string input and return a string result as output, the output's character set and collation are the same as those of the principal input value. For example, UPPER(X) returns a string with the same character string and collation as X. The same applies for INSTR(), LCASE(), LOWER(), LTRIM(), MID(), REPEAT(), REPLACE(), REVERSE(), RIGHT(), RPAD(), RTRIM(), SOUNDEX(), SUBSTRING(), TRIM(), UCASE(), and UPPER().


The REPLACE() function, unlike all other functions, always ignores the collation of the string input and performs a case-sensitive comparison.

If a string input or function result is a binary string, the string has the binary character set and collation. This can be checked by using the CHARSET() and COLLATION() functions, both of which return binary for a binary string argument:

| binary              | binary                |

For operations that combine multiple string inputs and return a single string output, the aggregation rules of standard SQL apply for determining the collation of the result:

  • If an explicit COLLATE Y occurs, use Y.

  • If explicit COLLATE Y and COLLATE Z occur, raise an error.

  • Otherwise, if all collations are Y, use Y.

  • Otherwise, the result has no collation.

For example, with CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END, the resulting collation is X. The same applies for UNION, ||, CONCAT(), ELT(), GREATEST(), IF(), and LEAST().

For operations that convert to character data, the character set and collation of the strings that result from the operations are defined by the character_set_connection and collation_connection system variables that determine the default connection character set and collation (see Section 10.1.5, “Connection Character Sets and Collations”). This applies only to CAST(), CONV(), FORMAT(), HEX(), and SPACE().

If there is any question about the character set or collation of the result returned by a string function, use the CHARSET() or COLLATION() function to find out:

| USER()         | CHARSET(USER()) | COLLATION(USER()) |
| test@localhost | utf8            | utf8_general_ci   |
| binary                   | binary                     |

User Comments
Sign Up Login You must be logged in to post a comment.