How the CAST() function works in Mariadb?

The MariaDB CAST() function is used to convert a value from one data type to another. This is particularly useful when you need to handle values that might be received as one data type but are required to be processed as another, ensuring data type compatibility in expressions and preventing data type errors.

Syntax

The syntax for the MariaDB CAST() function is as follows:

CAST(expr AS type)

The CAST() function takes an expression expr and converts it into the specified type.

Examples

Example 1: Casting a String to an Integer

This example demonstrates casting a numeric string to an integer:

SELECT CAST('123' AS UNSIGNED);

+-------------------------+
| CAST('123' AS UNSIGNED) |
+-------------------------+
|                     123 |
+-------------------------+

The output is 123 as the string '123' is cast to an unsigned integer.

Example 2: Casting a Date String to a Date Type

Here we cast a date string to a DATE type:

SELECT CAST('2023-04-05' AS DATE);

+----------------------------+
| CAST('2023-04-05' AS DATE) |
+----------------------------+
| 2023-04-05                 |
+----------------------------+

The output is a DATE object representing April 5, 2023.

Example 3: Casting an Integer to a String

This example shows how to cast an integer to a string:

SELECT CAST(500 AS CHAR);

+-------------------+
| CAST(500 AS CHAR) |
+-------------------+
| 500               |
+-------------------+

The output is '500' as the integer 500 is cast to a string.

Example 4: Using `CAST()` in a Table

To use CAST() with table data, let’s create a table with mixed data types:

DROP TABLE IF EXISTS example;
CREATE TABLE example (value CHAR(10));
INSERT INTO example VALUES ('100'), ('200'), ('300');

Now, let’s cast the value column to integers:

SELECT CAST(value AS UNSIGNED) FROM example;

+-------------------------+
| CAST(value AS UNSIGNED) |
+-------------------------+
|                     100 |
|                     200 |
|                     300 |
+-------------------------+

The output shows the values from the value column cast to unsigned integers.

Example 5: Casting a Decimal to an Integer

Let’s cast a decimal number to an integer:

SELECT CAST(123.456 AS UNSIGNED);

+---------------------------+
| CAST(123.456 AS UNSIGNED) |
+---------------------------+
|                       123 |
+---------------------------+

The output is 123 as the decimal 123.456 is cast to an unsigned integer, truncating the decimal part.

The CONVERT() function is used to convert a value to a specified type, similar to CAST(), but with additional options for character set conversion.
The FORMAT() function is used to format a number to a format like ‘#,###,###.##’, rounding it to a specified number of decimal places.
The STR_TO_DATE() function is used to convert a string to a DATE or DATETIME type, based on a specified format.

Conclusion

The CAST() function is an essential feature in MariaDB that provides flexibility in data type conversion, ensuring that operations on data are performed correctly and efficiently. It is a key function for data manipulation and is widely used in scenarios where explicit data type conversion is necessary. Understanding and utilizing the CAST() function can greatly enhance the robustness and reliability of SQL queries and procedures.