Cast from VARCHAR to INT - MySQL

mysql sql

My Current Data for

SELECT PROD_CODE FROM `PRODUCT`

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

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.

eggyal

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

Unsigned is working. From when did MySQL changed Integer datatype to Unsigned?

@emaillenin: The data types for casting are not the same as those for columns, as additional information is required on how to interpret data e.g. whether integers are signed or not.

Thanks for this info. MySQL documentation is chaotic for me so this helped a lot.

Note that on MariaDB CAST(PROD_CODE AS INT) works just fine.

@bsplosion: I don't disagree. There have been open feature requests since at least 2014 to have cast AS INT treated as AS SIGNED.

Jirka Kopřiva

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

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

Why ever would one use such a "hack" when there is a straightforward, documented, supported and recommended solution?

@eggyal TL;DR: the "hack" you are mentioning, is a straightforward, documented and recommended solution. - - - - - - - - - - - - - Long Version: From the manual: To cast a string to a number, you normally need do nothing other than use the string value in numeric context Although I'd use +0 instead of *1 since addition is faster.

@BrianStinar the accepted answer clearly shows that a solution with better semantics exists, but sometimes it is just convenient that a primitive value is implicitly casted to another primitive, especially when combining strings and numeric types. So randomly bashing programming languages for this feature's existence seems rather inappropriate.

Don't use these type of hacks! - very bad practise that will bite back much later when code is full of these and hard to revert

with this answer i found some times ending up with a float64 that why i will prefer using CONVERT('123456',UNSIGNED INTEGER)

Cast from VARCHAR to INT - MySQL

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US