MySQL CAST Function

MySQL CAST function takes an expression or a direct value or a value from a column of any type and produces a result value of a specified type

MySQL CAST Function syntax

CAST (expression/VALUE/COLUMN_NAME AS target_type)

MySQL CAST Function Examples

Creating table for demonstrating CAST Function

CREATE TABLE CONVERSION_FUNCTIONS

(

NUMERIC_VALUE INTEGER,

STRING VARCHAR(10 ),

DATE_VALUE DATE

);

INSERT INTO CONVERSION_FUNCTIONS (NUMERIC_VALUE, STRING, DATE_VALUE) VALUES

(111, 'AAA', '1991-10-10'),

(222, 'BBB', '1990-05-06'),

(333, 'CCC', '1999-06-07'),

(444, 'DDD', '1995-12-18');

Example 1

SELECT NUMERIC_VAL,CAST(NUMERIC_VAL AS CHAR) AS NEW_COLUMN

FROM CONVERSION_FUNCTIONS

Example 2

SELECT NUMERIC_VAL,NUMERIC_STRING_VAL,CAST(NUMERIC_VAL AS CHAR),CAST(NUMERIC_VAL AS DATE),CAST(NUMERIC_STRING_VAL AS DATE)

FROM CONVERSION_FUNCTIONS

Example 3

SELECT CAST(NUMERIC_STRING_VAL AS DECIMAL)

FROM CONVERSION_FUNCTIONS

MySQL CONVERT Function

MySQL CONVERT() function takes an expression or a direct value or a value from a column of any type and produces a result value of a specified type, similar to CAST().

CONVERT() with USING is used to convert data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the default character set to the corresponding string in the utf8 character set:

SELECT CONVERT('abc' USING utf8);

MySQL CONVERT Function syntax

CONVERT(expression/VALUE/COLUMN_NAME, target_type),

CONVERT(expression/VALUE/COLUMN_NAME USING transcoding_name)

The target_type for the result can be one of the following values:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

MySQL CONVERT Function Examples

Creating table for demonstrating CONVERT Function

CREATE TABLE CONVERSION_FUNCTIONS

(

NUMERIC_VALUE INTEGER,

STRING VARCHAR(10 ),

DATE_VALUE DATE

)

INSERT INTO `conversion_functions` (`NUMERIC_VALUE`, `STRING`, `DATE_VALUE`) VALUES

(111, 'AAA', '1991-10-10'),

(222, 'BBB', '1990-05-06'),

(333, 'CCC', '1999-06-07'),

(444, 'DDD', '1995-12-18');