Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 202.1Kb
Man Pages (Zip) - 307.5Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

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

13.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 11.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.