Cast from VARCHAR to INT - MySQL

303
votes

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

2
mysqlsql
What error does it report for each attempt? What are your inputs? It's supposed to fail the query if the cast fails for any record in the result set. At least, that's what the sql standard says, though MySql is notorious for breaking the safety rules in the standard. And, for the record, the 2nd and 4th listed samples are correct. – Joel Coehoorn

2 Answers

609
votes

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
69
votes

For casting varchar fields/values to number format can be little hack used:

SELECT (`PROD_CODE` * 1) AS `PROD_CODE` FROM PRODUCT`