8.4.1 Result Strings

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 a string input and return a string result as output, the output's character set and collation are the same as the principal input's. For example, UPPER(X) returns a string whose character string and collation are the same as that of X. The same applies for: INSTR(), LCASE(), LOWER(), LTRIM(), MID(), REPEAT(), REPLACE(), REVERSE(), RIGHT(), RPAD(), RTRIM(), SOUNDEX(), SUBSTRING(), TRIM(), UCASE(), UPPER().

For operations that combine multiple string inputs and return a single string output, SQL-99's ``aggregation rules'' apply. They are:

• If explicit COLLATE X occurs, then use X
• If explicit COLLATE X and COLLATE Y occur, then error
• Otherwise, if all collations are X, then use X
• Otherwise, the result has no collation

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

For operations that convert to character data, the result string's character set and collation are in the connection/literals character set and have the connection/literals collation. This applies for: CHAR(), CAST(), CONV(), FORMAT(). HEX(), SPACE().

User Comments

Add your own comment.