MySQL CAST Function

 MySQL CAST Function

Summary: in this tutorial, we will show you how to use the MySQL CAST function to convert a value of any type into a value with a specified type.

Introduction to MySQL CAST function

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

CAST(expression AS TYPE);

The CAST() the function converts a value of any type into a value that has a specified type. The target type can be any one of the following types: BINARY, CHAR, DATE, DATETIME, TIME,DECIMAL, SIGNED, UNSIGNED .

The CAST() the function is often used to return a value with a specified type for comparison in the WHERE, JOIN, and HAVING clauses.

Let’s take a look at some examples of using the CAST() function.

MySQL CAST function examples

In the following example, MySQL converts a string into an integer implicitly before doing a calculation:

SELECT (1 + '1')/2;

To explicitly convert a string into an integer, you use the CAST() function as the following statement:

SELECT (1 + CAST('1' AS UNSIGNED))/2;

The following statement explicitly converts an integer into a string and concatenate the string with another string:

SELECT CONCAT('MySQL CAST example #',CAST(2 AS CHAR));

Let’s take a look at the orders the table in the sample database.

See the following query:

SELECT orderNumber,
       requiredDate
FROM orders
WHERE requiredDate BETWEEN '2003-01-01' AND '2003-01-31';

The query selects orders whose required dates are in January 2003. The data type of the requireDate column is DATE, therefore, MySQL has to convert the literal strings: '2003-01-01' and '2003-01-31' into TIMESTAMP values before evaluating the WHERE condition.

However, to be safe, you can use CAST() function to explicitly convert a string into a TIMESTAMP value as follows:

SELECT orderNumber,
       requiredDate
FROM orders
WHERE requiredDate BETWEEN  CAST('2003-01-01' AS DATETIME)
                        AND CAST('2003-01-31' AS DATETIME);

The following statement converts DOUBLE values into CHAR values and uses the results as the arguments of the CONCAT function:

SELECT productName,
       CONCAT('Prices(',
               CAST(buyprice AS CHAR),
               ',',
                CAST(msrp AS CHAR),
      ')') prices
FROM products;

In this tutorial, you have learned how to use the MySQL CAST() function to convert a value with any type into a value with a specified type.