Both cast and convert functions in MySQL throw 'Truncated incorrect INTEGER value' when casting a string to an integer value:
select cast(mycolumn as unsigned) from mytable;
I would like to get the behavior of PHP and most other programming languages, which means I would like to cast all non-numeric strings to zero.
What is the most efficient way to do this in MySQL?
As a reference for anyone finding this question:
If you have MariaDB 10.0.5+ or MySQL 8+ then you can use the REGEXP_SUBSTR() function to keep only the first digits of a string.
SELECT REGEXP_SUBSTR('123foo', '^\\d+');
-> 123
Note: This isn't identical to CAST() as the result for 'foo123' is '' rather than 0.
This is the behaviour of mysql and frankly, there is nothing you can do about it. Although, pls note, in a select statement you only get a warning, not an error even if you are in strict sql mode. So, the select query kinda produces the output you expect, you just get a warning along with it.
If you want to use this in an update statement, however, then you need to turn off the strict sql mode -or even better: you should rethink your logíc to avoid converting text to number.
Try something like below, if you want to get rid of that warning(not necessary though)
select if(mycolumn regexp '[^0-9]',0,cast(mycolumn as unsigned));
int x = "Hello";in C or C# orint("Hello")in Python and see what you get. A lot of programming languages are statically typed or don't allow invalid type conversions. You might spend all day in PHP and JavaScript, but that doesn't mean dynamically typed languages that silently modify data when you make a bad type conversion are the default.atoiand some related functions in C/C++.CAST()andCONVERT()are. A C typecast would be(int) 'Hello'.atoihas a special algorithm to intentionally throw data away.