CONVERT vs CAST in MySQL

In SQL, we can convert the data type of values to another data type, This process of changing the data type is also known as explicit type casting. MySQL has two functions to change the data type:

1. CAST
2. CONVERT

We will learn what is explicit data type casting, uses of MySQL CAST and MySQL CONVERT functions and finally what is the difference between MySQL CAST and MySQL CONVERT  functions.

Why Do We Need Data Type Conversion?

To perform any operation on any data type basic constraint is that data type of input data should be the same.

For example, let’s say we have two values: 10 (Data Type: Number), and ‘20’ (Data Type: String). we want to add them together using the + operator, we will get an error saying that we have “incompatible data types”. To resolve this, we need to convert String to a Number Data Type.

1. MySQL CAST()

The CAST() function explicitly changes the data type of the input value to the specified target data type.

Syntax

1. CAST(input_value AS Output_data_type)

This function has two parameters, separated by the keyword AS. Both parameters are mandatory.

Parameter Details:

• Input_value: Any type of value or data that we want to convert
• Output_data_type: Any data type in which we want to change our Input value or data.

Below are the data types that we can use to change the data type:

1. MySQL CONVERT()

The CONVERT() function is the same as MySQL CAST(), explicitly changing the data type of the input to a specified target data type. Additionally, CONVERT() function can also be used to change the character set of the input value.

Syntax

CONVERT() has two syntaxes:

1. CONVERT(Input_value, Output_data_type) : used to change the data type
2. CONVERT(Input_value USING Output_character_set) : used to change the character set

Parameter Details:

• Input_value: Any type of value or data that we want to convert
• Output_data_type: Any data type in which we want to change our Input value or data.
• Output_character_set: Any supported character set in which we want to change our Input value.

Below are the few character sets in which we can convert our value:

Note:

We can get the character set list using below command in MySQL.

MySQL command:

                 show character set

Step-by-Step Explanation of Using CAST on String to Float

1. SELECT CAST('4' as DECIMAL(10,5));

In the above example DECIMAL(M, D):

        M total number of digits and

        D denotes the number of digits after the decimal point

Note:

The first argument should be greater than the second argument

Since we have specified the second parameter as 5, our resultant float value has five zeros after the decimal point.

Below are a few examples:

#1: MySQL cast to integer  / MySQL convert to integer

1. SELECT CAST("10" as SIGNED) ;
3. SELECT CONVERT("10", SIGNED) ;

#2: MySQL cast to char /  MySQL convert to char

1. SELECT CAST(45 as CHAR(10));
3. SELECT CONVERT(45 , CHAR(10));

#3: MySQL cast float to int  /  MySQL convert float to int

1. SELECT CAST(45.10 as UNSIGNED);
3. SELECT CONVERT(45.10 , UNSIGNED);

#4: MySQL string to date conversion

1. SELECT CAST('2018-10-05 00:00:00' as DATE);
3. SELECT CAST('2018-10-05 00:00:00' as DATETIME);

#5: MySQL cast string to date

1. SELECT CONVERT('2018-10-05 00:00:00' , DATE);
3. SELECT CONVERT('2018-10-05 00:00:00' , DATETIME);

#6: MySQL converts the character set to utf8

1. SELECT CONVERT('GotIt' using utf8);

Comparison Between MySQL CAST and CONVERT Functions: