Problem
My Current Information for
SELECT PROD_CODE FROM `PRODUCT`
is
PROD_CODE
2
5
7
8
22
10
9
11
I’ve tried each of the four queries, but none of them 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 of them produce syntax problems like the ones below:
In MySQL, what is the correct syntax for casting varchar to integer?
MySQL Version: 5.5.16
Asked by Lenin Raj Rajasekaran
Solution #1
Cast Functions and Operators: An Introduction describes how to use cast functions and operators.
Therefore, you should use:
SELECT CAST(PROD_CODE AS UNSIGNED) FROM PRODUCT
Answered by eggyal
Solution #2
A small hack can be used to convert varchar fields/values to numeric format:
SELECT (`PROD_CODE` * 1) AS `PROD_CODE` FROM PRODUCT`
Answered by Jirka Kopřiva
Post is based on https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql