MySQL string cast to unsigned

Question

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?

Instead of casting strings to integers, cast integers to strings. [Zip codes are not integers](http://stackoverflow.com/q/24192830/). — Dour High Arch, Jun 16 '14 at 21:18
@DourHighArch, agree totally. Shoot whoever stored the ZIP codes as integers in the first place. When was the last time you ever needed to find the sum of ZIP codes or calculated compound interest on a ZIP code? — Joshua Huber, Jun 17 '14 at 00:15

score 1 · Answer 1 · answered Jun 16 '14 at 21:19

1

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.

answered Jun 16 '14 at 21:19

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jun 16 '14 at 21:27

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

MySQL string cast to unsigned

2 Answers2