![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196969.webp)
Cast Functions and Operators
949
•
SIGNED [INTEGER]
•
TIME
•
UNSIGNED [INTEGER]
BINARY
produces a string with the
BINARY
data type. See
Section 11.1.6.2, “The
BINARY
and
VARBINARY
Types”
for a description of how this affects comparisons. If the optional length
N
is
given,
BINARY(N)
causes the cast to use no more than
N
bytes of the argument. As of MySQL
5.0.17, values shorter than
N
bytes are padded with
0x00
bytes to a length of
N
.
CHAR(N)
[886]
causes the cast to use no more than
N
characters of the argument.
The
DECIMAL
type is available as of MySQL 5.0.8.
CAST()
[948]
and
CONVERT(... USING ...)
[948]
are standard SQL syntax. The non-
USING
form of
CONVERT()
[948]
is ODBC syntax.
CONVERT()
[948]
with
USING
is used to convert data between different character sets. In MySQL,
transcoding names are the same as the corresponding character set names. For example, this
statement converts the string
'abc'
in the default character set to the corresponding string in the
utf8
character set:
SELECT CONVERT('abc' USING utf8);
Normally, you cannot compare a
BLOB
value or other binary string in case-insensitive fashion
because binary strings have no character set, and thus no concept of lettercase. To perform a case-
insensitive comparison, use the
CONVERT()
[948]
function to convert the value to a nonbinary string.
Comparisons of the result use the string collation. For example, if the character set of the result has a
case-insensitive collation, a
LIKE
[896]
operation is not case sensitive:
SELECT 'A' LIKE CONVERT(
blob_col
USING latin1) FROM
tbl_name
;
To use a different character set, substitute its name for
latin1
in the preceding statement.
To specify a particular collation for the converted string, use a
COLLATE
clause following the
CONVERT()
[948]
call, as described in
Section 10.1.9.2, “
CONVERT()
and
CAST()
”
. For example, to
use
latin1_german1_ci
:
SELECT 'A' LIKE CONVERT(
blob_col
USING latin1) COLLATE latin1_german1_ci
FROM
tbl_name
;
CONVERT()
[948]
can be used more generally for comparing strings that are represented in different
character sets.
LOWER()
[890]
(and
UPPER()
[895]
) are ineffective when applied to binary strings (
BINARY
,
VARBINARY
,
BLOB
). To perform lettercase conversion, convert the string to a nonbinary string:
mysql>
SET @str = BINARY 'New York';
mysql>
SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York | new york |
+-------------+-----------------------------------+
The cast functions are useful when you want to create a column with a specific type in a
CREATE
TABLE ... SELECT
statement:
CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);
The functions also can be useful for sorting
ENUM
columns in lexical order. Normally, sorting of
ENUM
columns occurs using the internal numeric values. Casting the values to
CHAR
results in a lexical sort:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...