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.