'How to know CAST failed in MySQL

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';
mysqlcasting


Solution 1:[1]

The SHOW WARNINGS statement and the @@WARNINGS system variable are the built in methods to do this. There is no mechanism to automatically upgrade all warnings to errors, but there are some things you can do.

You may want to start MySQL with the --show-warnings option, although that might just display the count of warnings with the row count. I can't recall anymore. I don't know if there is a my.ini option for this option. There's also the --log-warnings option, which I believe does have an option in the ini/cnf file. If you're executing a script or using the CLI, the \W command turns show warnings on and \w turns them off for (IIRC) the current connection.

You may also want to look at the SQL mode. TRADITIONAL is probably the most like a normal RDBMS, but it's kind of a rats nest of options. The STRICT modes are what you're most likely after, but read through that page. Most apps built on MySQL take advantage of the (non-deterministic) GROUP BY extensions that bite just about everybody moving to or away from MySQL, and TRADITIONAL enables ONLY_FULL_GROUP_BY, which effectively disables those extensions and the RDBMS doesn't support the OVER() clause. I don't know if silently succeeding at typecasting will abort a transaction even in traditional/strict mode, however.

MySQL is kind of a mine field of these kinds of issues (e.g., zero dates) so it kind of has a poor reputation with DBAs, especially those who worked with v3.x or v4.x.

Solution 2:[2]

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)

Solution 3:[3]

You could e.g. check the warning_count variable:

MySQL [test]> SELECT CAST(0 AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count;
+-------+-----------------+
| CAST1 | @@warning_count |
+-------+-----------------+
|     0 |               0 |
+-------+-----------------+
1 row in set (0.01 sec)

MySQL [test]> SELECT CAST('Banana' AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count;
+-------+-----------------+
| CAST1 | @@warning_count |
+-------+-----------------+
|     0 |               1 |
+-------+-----------------+
1 row in set, 1 warning (0.00 sec)

There's a caveate though: the warning count is only reset per statement, not per result row, so if CAST() gets executed mutiple times, e.g. for each result row, the counter will go up on each failed invocation.

Also warnings don't seem to get reset on successful queries that don't touch any tables, so in the example above a 2nd 

SELECT CAST(0 AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count;

will still show 1 warning, while e.g.

SELECT CAST(0 AS UNSIGNED INTEGER) AS 'CAST1', @@warning_count
  FROM mysql.user LIMIT 1;

will correctly reset it to 0 ...

Solution 4:[4]

Well, you could incorporate @@warning_count but somehow create workaround for its buggy functionality.

Take a look at this code below. Yes, I know it's ugly, but it works.

SELECT
        IF(WarningCount = 0, ConversionResult, NULL)
FROM (
    SELECT 
            CAST('banana' AS DECIMAL(10, 6)) AS ConversionResult
        ,   @@warning_count AS WarningCount
    FROM <any non empty table>
    LIMIT 1
) AS i;

In inner SELECT I'm getting 1 row (LIMIT 1) from any existing table. I'm converting string ('banana') and get WarningCount. In outer SELECT I'm checking WorkingCount and if it's equal to 0 (conversion successful) then returning converted value.

Solution 5:[5]

I would suggest to use such function:

drop function if exists to_number;
delimiter $$
create  function to_number (number varchar(10)) returns int
begin
    declare error_message varchar(45);
    if (number regexp ('^[+-]?[0-9]*([0-9]\\.|[0-9]|\\.[0-9])[0-9]*(e[+-]?[0-9]+)?$'))
    then
      return number;
    else
      set error_message = concat('The given value "', number, '" is not a number.');
      signal sqlstate '45000' set message_text = error_message;
    end if;
end;

It rises an error if the given value is not number or returns the number value.

Solution 6:[6]

If you are trying to determine how many values in a varchar are numbers you can try:

select count(*),
       sum(is_num)
 from (select case when cast(cast(ar_number as unsigned) as char) = ar_number then 1 else 0  end as is_num
 from the_table) as t1;

Solution 7:[7]

SQL Server supports the try_cast function

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bacon Bits
Solution 2 Gordon Linoff
Solution 3 Hartmut Holzgraefe
Solution 4 Marek J
Solution 5 PavelPraulov
Solution 6 John Darrah
Solution 7 Raul

Related Questions