Oracle 5.0, Reference Manual

Page: 809 / 3232

Operations Affected by Character Set Support
789
for
INSTR()
[889] ,
LCASE()
[889] ,
LOWER()
[890] ,
LTRIM()
[891] ,
MID()
[891] ,
REPEAT()
[892] ,
REPLACE()
[892] ,
REVERSE()
[892] ,
RIGHT()
[892] ,
RPAD()
[892] ,
RTRIM()
[893] ,
SOUNDEX()
[893] ,
SUBSTRING()
[894] ,
TRIM()
[894] ,
UCASE()
[895] , and
UPPER()
[895] .
Note: The
REPLACE()
[892] 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 no character set or collation. This can
be checked by using the
CHARSET()
[958] and
COLLATION()
[959] functions, both of which return
binary
to indicate that their argument is a binary string:
mysql> SELECT CHARSET(BINARY 'a'), COLLATION(BINARY 'a');
+---------------------+-----------------------+
| CHARSET(BINARY 'a') | COLLATION(BINARY 'a') |
+---------------------+-----------------------+
| 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 X
occurs, use
X
.
• If explicit
COLLATE X
and
COLLATE Y
occur, raise an error.
• Otherwise, if all collations are
X
, use
X
.
• 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
,
||
[881] ,
CONCAT()
[887] ,
ELT()
[888] ,
GREATEST()
[878] ,
IF()
[883] , and
LEAST()
[879] .
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
[442] and
collation_connection
[443] system variables. This applies only to
CAST()
[948] ,
CONV()
[909] ,
FORMAT()
[889] ,
HEX()
[889] ,
SPACE()
[893] . Before MySQL 5.0.15, it also
applies to
CHAR()
[886] .
If you are uncertain about the character set or collation of the result returned by a string function, you
can use the
CHARSET()
[958] or
COLLATION()
[959] function to find out:
mysql> SELECT USER(), CHARSET(USER()), COLLATION(USER());
+----------------+-----------------+-------------------+
| USER() | CHARSET(USER()) | COLLATION(USER()) |
+----------------+-----------------+-------------------+
| test@localhost | utf8 | utf8_general_ci |
+----------------+-----------------+-------------------+
10.1.9.2.
CONVERT()
and
CAST()
CONVERT()
[948] provides a way to convert data between different character sets. The syntax is:
CONVERT( expr USING transcoding_name )
In MySQL, transcoding names are the same as the corresponding character set names.
Examples:
SELECT CONVERT(_latin1'Müller' USING utf8);
INSERT INTO utf8table (utf8column)
SELECT CONVERT(latin1field USING utf8) FROM latin1table;
CONVERT(... USING ...)
[948] is implemented according to the standard SQL specification.