How the CAST() function works in Mariadb?

The CAST() function is a conversion function that allows you to convert a value from one data type to another.

Posted on

The CAST() function is a conversion function that allows you to convert a value from one data type to another. The CAST() function can be used to change the type, format, or precision of a value, or to avoid type mismatch errors.

Syntax

The syntax of the CAST() function is as follows:

CAST(value AS type)

Where:

  • value is an expression that returns a value to be converted.
  • type is the target data type to convert the value to.

The type can be one of the following:

  • BINARY[(N)]: A binary string with a maximum length of N bytes. If N is not specified, it defaults to 1. If the value is longer than N bytes, it is truncated. If the value is shorter than N bytes, it is padded with 0x00 bytes.
  • CHAR[(N)]: A character string with a maximum length of N characters. If N is not specified, it defaults to 1. If the value is longer than N characters, it is truncated. If the value is shorter than N characters, it is padded with spaces.
  • DATE: A date value in the format ‘YYYY-MM-DD’. If the value is not a valid date, it returns NULL.
  • DATETIME: A datetime value in the format ‘YYYY-MM-DD HH:MM:SS’. If the value is not a valid datetime, it returns NULL.
  • DECIMAL[(M[,D])]: A decimal number with a maximum precision of M digits and a maximum scale of D digits. If M is not specified, it defaults to 10. If D is not specified, it defaults to 0. If the value has more digits than M, it is rounded. If the value has more decimal places than D, it is truncated.
  • DOUBLE: A double-precision floating-point number. If the value is not a valid number, it returns NULL.
  • FLOAT: A single-precision floating-point number. If the value is not a valid number, it returns NULL.
  • INTEGER: An integer number. If the value is not a valid integer, it returns NULL.
  • JSON: A JSON value. If the value is not a valid JSON, it returns NULL.
  • SIGNED [INTEGER]: A signed integer number. If the value is not a valid integer, it returns NULL.
  • TIME: A time value in the format ‘HH:MM:SS’. If the value is not a valid time, it returns NULL.
  • UNSIGNED [INTEGER]: An unsigned integer number. If the value is not a valid integer, it returns NULL.

Examples

Example 1: Converting a string to a date

In this example, we use the CAST() function to convert a string value to a date value. We use the DATE type as the target type.

SELECT CAST('2021-04-15' AS DATE) AS date;

The output is:

+------------+
| date       |
+------------+
| 2021-04-15 |
+------------+

Example 2: Converting a decimal to an integer

In this example, we use the CAST() function to convert a decimal value to an integer value. We use the INTEGER type as the target type.

SELECT CAST(3.14 AS INTEGER) AS integer;

The output is:

+---------+
| integer |
+---------+
| 3       |
+---------+

Example 3: Converting a binary to a char

In this example, we use the CAST() function to convert a binary value to a char value. We use the CHAR(4) type as the target type.

SELECT CAST(0x41424344 AS CHAR(4)) AS char;

The output is:

+------+
| char |
+------+
| ABCD |
+------+

Some other functions that are related to the CAST() function are:

  • CONVERT(): Returns a value converted to a specified data type, using an optional format. The syntax is CONVERT(value, type[, format]).
  • BINARY(): Returns a binary string representation of a value. The syntax is BINARY(value).
  • FORMAT(): Returns a value formatted with a specified number of decimal places and a locale-specific decimal point and thousands separator. The syntax is FORMAT(value, decimals[, locale]).
  • PARSE_JSON(): Returns a JSON value parsed from a string. The syntax is PARSE_JSON(string).

For example, you can use the CONVERT() function to achieve the same result as the first example of the CAST() function, but with a different syntax:

SELECT CONVERT('2021-04-15', DATE) AS date;

Conclusion

The CAST() function is a useful function to convert a value from one data type to another. The CAST() function can be used to change the type, format, or precision of a value, or to avoid type mismatch errors. The CAST() function takes a value and a target type as arguments, and returns a value of the target type. The target type can be one of the supported data types, such as BINARY, CHAR, DATE, DATETIME, DECIMAL, DOUBLE, FLOAT, INTEGER, JSON, SIGNED, TIME, or UNSIGNED. The CAST() function can be combined with other functions to perform various conversions and operations.