Suppress CAST() warnings in MySQL?

Question

I'm using MySQL in strict mode (SET sql_mode = 'STRICT_TRANS_TABLES') to convert all warnings to errors. However, I have a query that is expected to create warnings because it tries to convert a VARCHAR field that might be empty or contain letters to an integer.

Example:

mysql> select CAST("123b" AS SIGNED);
+------------------------+
| CAST("123b" AS SIGNED) |
+------------------------+
|                    123 |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '123b' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

Is there a way to suppress the warning caused by the CAST() without disabling strict mode? Or alternatively, can the strict mode be disabled for a single query or function (something like the @ operator in PHP) without calling SET twice to temporarily switch off the strict mode?

Background: I have a table with street numbers. Most of them are numeric but some contain letters at the end. To implement a simplistic "natural sort" I'd like to use ORDER BY CAST (StreetNr AS SIGNED), StreetNr and the value returned by CAST() would be just fine for 1st level sorting.

Answer 1

I'm assuming the problem is that you are trying to insert data from one table into another using a query like this:

INSERT INTO ...
SELECT ...
FROM ...
WHERE ...
ORDER BY ...

And the inserts are failing because of the CAST() problem you described in your question.

Is that accurate?

If so, the easiest way around this is to use INSERT IGNORE. That syntax is useful for ignoring duplicate key errors, but it can also be used to ignore the CAST() errors that are affecting you.

Your updated query would look something like this:

INSERT IGNORE INTO target_table
SELECT ...
FROM source_table
WHERE ...
ORDER BY CAST (StreetNr AS SIGNED), StreetNr

Answer 2

I had the same issue with ordering and used regexp first:

ORDER BY CAST(CONCAT('0', REGEXP_REPLACE(StreetNr, '[^0-9]', '')) AS INTEGER)

The concat-0 is to handle an empty string. (My server is MariaDB, but it should be the same as MySQL.)