CODEWITHSUNDEEP
mysql
Roland Seuhs
Roland Seuhs

Reputation: 1995

Mysql: How to cast to integer while avoiding 'Truncated incorrect INTEGER value' for non-numeric values

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?

Upvotes: 4

Views: 2003

Answers (3)

Ramesh
Ramesh

Reputation: 127

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));

Upvotes: 1

chris
chris

Reputation: 3193

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.

Upvotes: 2

Shadow
Shadow

Reputation: 34231

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.

Upvotes: 1

Related Questions