Mysql – Cast from VARCHAR to INT – MySQL

mysqlsql

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

Best Solution

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