sql - Cast from VARCHAR to INT - MySQL

My Current Data for

SELECT PROD_CODE FROM `PRODUCT`

is

PROD_CODE
2
5
7
8
22
10
9
11

I have tried all the four queries and none work. (Ref)

SELECT CAST(PROD_CODE) AS INT FROM PRODUCT;

SELECT CAST(PROD_CODE AS INT) FROM PRODUCT;

SELECT CAST(PROD_CODE) AS INTEGER FROM PRODUCT;


SELECT CAST(PROD_CODE AS INTEGER) FROM PRODUCT;

All throw syntax errors such as below:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near ') AS INT FROM PRODUCT LIMIT 0, 30' at line 1

You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use
near 'INTEGER) FROM PRODUCT LIMIT 0, 30' at line 1

What is the right syntax to cast varchar to integer in MySQL?

MySQL Version: 5.5.16

Answer

As described in Cast Functions and Operators:

The 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]

Therefore, you should use:

SELECT CAST(PROD_CODE AS UNSIGNED) FROM PRODUCT