CAST()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Takes a value of one type and converts it to the value of another of type. See CONVERT.
USAGE
CAST(value AS type)
Argument Name | Description |
|---|---|
| The value to cast |
| The type to use for the result |
DETAILS
CAST() is a type-conversion function that casts an expression of one type to a value of another type.
The arguments use the AS clause to specify the target type using type keywords (not strings).
A NULL is returned if the value to cast is NULL.
EXAMPLES
CREATE TABLE cast_example (
example VARCHAR(32)
);
INSERT INTO cast_example VALUES
('apple '), ('Apricot '), ('berry'),
('Cheese '), ('Dairy '), ('eggs ');
Notice that casting a VARCHAR with trailing spaces to a CHAR does not auto-remove the trailing spaces (since the space removal only happens when a value is retrieved from a field):
SELECT CONCAT(example, '!') as 'example!',
LENGTH(example) AS len,
LENGTH(CAST(example AS CHAR)) AS cast_len
FROM cast_example ORDER BY example;
+------------+------+----------+
| example! | len | cast_len |
+------------+------+----------+
| apple ! | 6 | 6 |
| Apricot ! | 9 | 9 |
| berry! | 5 | 5 |
| Cheese ! | 9 | 9 |
| Dairy ! | 7 | 7 |
| eggs ! | 5 | 5 |
+------------+------+----------+
You can make case significant in a sort by casting to a BINARY type:
SELECT *
FROM cast_example
ORDER BY CAST(example AS BINARY);
+-----------+
| example |
+-----------+
| Apricot |
| Cheese |
| Dairy |
| apple |
| berry |
| eggs |
+-----------+
CREATE TABLE timezone (
t TIMESTAMP
);
INSERT INTO timezone VALUES ('2020-10-13T23:23:28');
SELECT CAST(t AS DATETIME)
FROM timezone;
+---------------------+
| CAST(t AS DATETIME) |
+---------------------+
| 2020-10-13 23:23:28 |
+---------------------+
ERROR HANDLING
Unknown data type
The CAST() function requires target type as one of the supported types. If an unknown type is provided as target type, an error is generated:
SELECT CAST(example AS NULL)
FROM cast_example;
ERROR 1064 (HY000): [32768] syntax error: syntax error near "NULL"
LINE: SELECT CAST(example AS NULL) FROM cast_example
^