Cast from VARCHAR to INT

 

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

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

    对于将varchar字段/值转换为数字格式，可以使用一点小技巧： 

    SELECT (`PROD_CODE` * 1) AS `PROD_CODE` FROM PRODUCT`
    链接地址: http://www.djcxy.com/p/93652.html

    上一篇: PDO准备好的报表有多安全

    下一篇: 从VARCHAR转换为INT