Edit Question - KnowledgeHub

Title Body

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.

Tags (comma-separated) Cancel