CODEWITHSUNDEEP
mysqlstringcastinginteger
Mike C
Mike C

Reputation: 2049

MySQL string cast to unsigned

If I have a string that starts with a number, then contains non-numeric characters, casting this string to an integer in MySQL will cast the first part of the string, and give no indication that it ran into any problems! This is rather annoying.

For example:

SELECT CAST('123' AS UNSIGNED) AS WORKS,
       CAST('123J45' AS UNSIGNED) AS SHOULDNT_WORK,
       CAST('J123' AS UNSIGNED) AS DOESNT_WORK

returns:

+-------------+---------------+-------------+
|    WORKS    | SHOULDNT_WORK | DOESNT_WORK |
+-------------+---------------+-------------+
|     123     |      123      |      0      |
+-------------+---------------+-------------+

This doesn't make any sense to me, as clearly, 123J45 is not a number, and certainly does not equal 123. Here's my use case:

I have a field that contains (some malformed) zip codes. There may be mistypes, missing data, etc., and that's okay from my perspective. Because of another table storing Zip Codes as integers, when I join the tables, I need to cast the string Zip Codes to integers (I would have to pad with 0s if I was going the other way). However, if for some reason there's an entry that contains 6023JZ1, in no way would I want that to be interpreted as Zip Code 06023. I am much happier with 6023JZ1 getting mapped to NULL. Unfortunately, IF(CAST(zipcode AS UNSIGNED) <= 0, NULL, CAST(zipcode AS UNSIGNED)) doesn't work because of the problem discussed above.

How do I control for this?

Upvotes: 0

Views: 7440

Answers (2)

spencer7593
spencer7593

Reputation: 108500

One options is to test for just digit characters 0 thru 9 for the entire length of the string:

zipstr REGEXP '^[0-9]+$'

Based on the result of that boolean, you could return the integer value, or a NULL.

SELECT IF(zipstr REGEXP '^[0-9]+$',zipstr+0,NULL) AS zipnum ...

(note: the addition of zero is an implicit conversion to numeric)

Another option is to do the conversion like you are doing, and cast the numeric value back to character, and compare to the original string, to return a boolean:

CAST( zipstr+0 AS CHAR) = zipstr

(note: this second approach does allow for a decimal point, e.g.

CAST( '123.4'+0 AS CHAR ) = '123.4'  => 1

which may not be desirable if you are looking for just a valid integer

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Use a regular expression:

select (case when val rlike '[0-9][0-9][0-9][0-9][0-9]' then cast(val as unsigned)
        end)

Many people consider it a nice feature that MySQL does not automatically produce an error when doing this conversion.

Upvotes: 1

Related Questions