How to know CAST failed in MySQL

Question

Could somebody tell me how I can detect if a cast failed in MySQL using CAST() function?

These two lines return the same value: 0.

SELECT CAST('Banana' AS UNSIGNED INTEGER) AS 'CAST1';
SELECT CAST('0' AS UNSIGNED INTEGER) AS 'CAST2';

Answer 1

You can use regular expressions to validate the data before the conversion:

select (case when val regexp '^[0-9]+$' then cast(val as unsigned integer) end)