# CAST

## Syntax

```sql
CAST(expr AS type)
```

## Description

The `CAST()` function takes a value of one [data type](https://mariadb.com/docs/server/reference/data-types) and produces a value of another data type, similar to the [CONVERT()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/convert) function.

The type can be one of the following values:

* [BINARY](https://mariadb.com/docs/server/reference/data-types/string-data-types/binary)
* [CHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/char)
* [DATE](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/date)
* [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime)
* [DECIMAL](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal)
* [DOUBLE](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/double)
* [FLOAT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/float)
* [INTEGER](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int) (short for `SIGNED INTEGER` )
* [SIGNED \[INTEGER\]](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/integer)
* [UNSIGNED \[INTEGER\]](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/integer)
* [TIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/time)
* [VARCHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar)
* [XMLTYPE](https://mariadb.com/docs/server/reference/data-types/string-data-types/xmltype) (Available starting with MariaDB 12.3)

The main difference between `CAST` and [CONVERT()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/convert) is that [CONVERT(expr,type)](https://mariadb.com/docs/server/reference/sql-functions/string-functions/convert) is ODBC syntax, while `CAST(expr as type)` and [CONVERT(... USING ...)](https://mariadb.com/docs/server/reference/sql-functions/string-functions/convert) are SQL92 syntax.

To cast a value to a string data type while specifying the character set, use this extended syntax:

```sql
 CAST(expr AS CHAR CHARACTER SET name)
```

See the [examples for casting to character sets](#casting-to-character-sets).

{% hint style="info" %}
Using an introducer like `_utf8mb4'text'` is often more efficient than `CAST('text' AS CHAR CHARACTER SET utf8mb4)`, but `CAST` is necessary when converting data types (like numbers to strings) into a specific encoding. See [this example](#using-an-introducer-inside-a-cast), too.
{% endhint %}

You can use the `CAST()` function with the `INTERVAL` keyword.

This introduced an incompatibility with previous versions of MariaDB, and all versions of MySQL (see the example below).

## Examples

### Simple Casts

```sql
SELECT CAST("abc" AS BINARY);
SELECT CAST("1" AS UNSIGNED INTEGER);
SELECT CAST(123 AS CHAR CHARACTER SET utf8)
```

{% hint style="warning" %}
When you casts to [CHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/char) without specifying the character set, the [collation\_connection](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#collation_connection) character set collation is used. When used with `CHAR CHARACTER SET`, the default collation for that character set is used.
{% endhint %}

```sql
SELECT COLLATION(CAST(123 AS CHAR));
+------------------------------+
| COLLATION(CAST(123 AS CHAR)) |
+------------------------------+
| latin1_swedish_ci            |
+------------------------------+

SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8));
+-------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8)) |
+-------------------------------------------------+
| utf8_general_ci                                 |
+-------------------------------------------------+
```

If you also want to change the collation, you have to use the `COLLATE` operator:

```sql
SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8) 
  COLLATE utf8_unicode_ci);
+-------------------------------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci) |
+-------------------------------------------------------------------------+
| utf8_unicode_ci                                                         |
+-------------------------------------------------------------------------+
```

Using `CAST()` to order an [ENUM](https://mariadb.com/docs/server/reference/data-types/string-data-types/enum) field as a [CHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/char) rather than the internal numerical value:

```sql
CREATE TABLE enum_list (enum_field enum('c','a','b'));

INSERT INTO enum_list (enum_field) 
VALUES('c'),('a'),('c'),('b');

SELECT * FROM enum_list 
ORDER BY enum_field;
+------------+
| enum_field |
+------------+
| c          |
| c          |
| a          |
| b          |
+------------+

SELECT * FROM enum_list 
ORDER BY CAST(enum_field AS CHAR);
+------------+
| enum_field |
+------------+
| a          |
| b          |
| c          |
| c          |
+------------+
```

The following `CAST()` gives warnings, because `x'aa'` and `'X'aa'` don't behave as a number. In all versions of MySQL, no warnings are triggered because they erroneously behave as a number:

```sql
SELECT CAST(0xAA AS UNSIGNED), CAST(x'aa' AS UNSIGNED), CAST(X'aa' AS UNSIGNED);
+------------------------+-------------------------+-------------------------+
| CAST(0xAA AS UNSIGNED) | CAST(x'aa' AS UNSIGNED) | CAST(X'aa' AS UNSIGNED) |
+------------------------+-------------------------+-------------------------+
|                    170 |                       0 |                       0 |
+------------------------+-------------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)

Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'
Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'
```

### Casting to Intervals

```sql
SELECT CAST(2019-01-04 AS INTERVAL DAY_SECOND(2)) AS "Cast";

+-------------+
| Cast        |
+-------------+
| 00:20:14.00 |
+-------------+
```

### Casting to Character Sets

#### Casting a Literal to a Specific Character Set

```sql
-- Converts the literal string 'abc' to the utf8mb4 character set
SELECT CAST('abc' AS CHAR CHARACTER SET utf8mb4);
```

#### Casting a Binary Literal to a Readable Character Set

```sql
-- Converts a hexadecimal (binary) literal to a UTF-8 string
SELECT CAST(0x68656c6c6f AS CHAR CHARACTER SET utf8mb4);
```

#### Using an Introducer Inside a CAST

```sql
-- Telling the parser the input is latin1, then casting the result to utf8mb4
SELECT CAST(_latin1'text' AS CHAR CHARACTER SET utf8mb4);
```

## See Also

* [Supported data types](https://mariadb.com/docs/server/reference/data-types)
* [Microseconds in MariaDB](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/microseconds-in-mariadb)
* [String literals](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/string-literals)
* [COLLATION()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/collation)
* [CONVERT()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/convert)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}