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

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, Aug 26 '12 at 01:28

score 716 · Accepted Answer · answered Aug 26 '12 at 01:35

716

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

answered Aug 26 '12 at 01:35

eggyal

122,705
18
212
237

11

Unsigned is working. From when did MySQL changed Integer datatype to Unsigned? – Raj Aug 26 '12 at 01:36
26

@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. – eggyal Aug 26 '12 at 01:38
18

Thanks for this info. MySQL documentation is chaotic for me so this helped a lot. – Racky Oct 29 '13 at 08:09
I was having issues with CAST(num_col AS DOUBLE(10,2).. Later I changed it to DECIMAL(10,2) and it worked. Tnanks – Nava Bogatee Jun 11 '17 at 16:16
3

Note that on MariaDB `CAST(PROD_CODE AS INT)` [works](https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=788d34d534bea6be08e605c9343ab4ce) just fine. – Paul Spiegel Oct 03 '19 at 15:30
If you are expecting negative values in your column data, then use this `SELECT CAST(PROD_CODE AS SIGNED) FROM PRODUCT` – Devner Nov 11 '19 at 05:26
this converts to long not int – Imad Nov 17 '19 at 13:42
not working in case of aggregate functions *CAST(SUM(O.points) AS UNSIGNED) as week_points* – Ankit Badiya Apr 14 '20 at 10:00
@AnkitBadiya: you need to cast *before* you sum `SUM(CAST(O.points AS UNSIGNED)) AS week_points`. – eggyal Apr 14 '20 at 13:09
This is really bizarre - MySQL has no problem performing an implicit cast of a `VARCHAR` to an `INT`, but when you ask it to do exactly that it barks? And better yet, there's no way to explicitly check to see what that implicit operation will yield before performing it? Keepin' it classy, MySQL... – bsplosion Aug 19 '21 at 23:03
@bsplosion: Can you give an example of MySQL implicitly casting `VARCHAR` to `INT`? – eggyal Aug 20 '21 at 01:04
1

@eggyal Sure - just insert a `VARCHAR` into an `INT` column: `create table implicit_conversion (int_col int); insert into implicit_conversion values ('12345');`. This also works if you create a table with a `VARCHAR` column and use that to select into an `INT` column on another table. The mechanism by which MySQL converts those doesn't seem to be exposed to the user. – bsplosion Aug 21 '21 at 18:05
@bsplosion: a column declared `INT` is implicitly signed unless explicitly declared `UNSIGNED`; thus the implicit casts to which you refer are identical to explicitly casting `AS SIGNED`. – eggyal Aug 21 '21 at 18:14
@eggyal Okay, cool, but why is it impossible to implicitly sign a cast? It's very strange that MySQL doesn't allow you to cast columns as data types which align with column types - you can't run `CAST(PROD_CODE AS INT)`, but you sure can do that exact same thing with an insert. If MySQL is implicitly going to handle `INT` as `SIGNED`, it'd be nice if they'd do it consistently. The fact this answer currently has 612 votes really speaks to their approach being arcane. – bsplosion Aug 22 '21 at 21:53
3

@bsplosion: I don't disagree. There have been [open feature requests since at least 2014](https://bugs.mysql.com/bug.php?id=73054) to have cast `AS INT` treated as `AS SIGNED`. – eggyal Aug 22 '21 at 22:03
1

@bsplosion: It's also notable that `AS INTEGER` is shorthand for `AS SIGNED` in [MariaDB](https://mariadb.com/kb/en/cast/). – eggyal Aug 22 '21 at 22:10

score 99 · Answer 2 · answered Mar 01 '16 at 19:00

99

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

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

answered Mar 01 '16 at 19:00

Jirka Kopřiva

2,939
25
28

15

Why ever would one use such a "hack" when there is a straightforward, documented, supported and recommended solution? – eggyal Apr 08 '16 at 07:52
53

@eggyal **TL;DR:** the "hack" you are mentioning, is a straightforward, documented and recommended solution. - - - - - - - - - - - - - **Long Version**: From [the manual](http://dev.mysql.com/doc/refman/5.7/en/cast-functions.html): `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. – Mindwin Remember Monica Oct 24 '16 at 14:08
9

@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. – Felix K. Sep 23 '18 at 18:45
1

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 – nights Sep 11 '19 at 09:28
4

with this answer i found some times ending up with a float64 that why i will prefer using `CONVERT('123456',UNSIGNED INTEGER)` – Isaac Weingarten Jun 23 '20 at 21:06
Sometimes this is a very elegant solution. – The Onin Jan 09 '21 at 09:50
For now I will use it. I guess its good as long as the string value to be hacked will always looks like a number else use another approach – Ishmael Mavor Raines Oct 19 '22 at 09:57

Cast from VARCHAR to INT - MySQL

2 Answers2

Linked

Related