Cast from VARCHAR to INT - MySQL

Question

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

User · Accepted 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

User · Answer

For casting varchar fields values to number format can be little hack used   SELECT   PROD CODE    1  AS  PROD CODE  FROM PRODUCT

[mysql] Cast from VARCHAR to INT - MySQL

Examples related to mysql

Examples related to sql