0

From my understanding, and I am probably wrong here, but doesn't the ISNUMERIC() return a 1 if whatever we are looking at is a numeric data type? And since most zip codes are saved as varchar data types, shouldn't it then return a 0? I looked up the documentation and that's what it says there to me, what am I missing here? I get zip codes are numbers, but because they are saved as a string shouldn't that make a difference? Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jobobo1
  • 1
  • 1
  • 1
    No, `ISNUMERIC()` looks at the actual values, not the data types. Yes, the documentation is confusing about this if you read it the wrong way. It's also kind of a naiive, so it's generally best to use something more like `TRY_CONVERT()` instead. – Joel Coehoorn Jun 28 '21 at 14:04
  • [Docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15) could indeed use better wording: "Determines whether an expression is a valid numeric type". PHP, for instance, has a similarly named function described as "Finds whether a variable is a number or a numeric string". – Álvaro González Jun 28 '21 at 14:07
  • Does this answer your question? [Why is ISNUMERIC(',') true?](https://stackoverflow.com/questions/24053613/why-is-isnumeric-true) – GSerg Jun 28 '21 at 14:09
  • Thank you all, it makes much more sense now. – Jobobo1 Jun 28 '21 at 16:03

1 Answers1

1

ISNUMERIC() is specifically to look at strings, not at things already stored as numbers.

However, I don't recommend using it. For instance, '3.1e5' is considered numeric.

Instead, use try_convert():

try_convert(int, zip)

This returns NULL if the column cannot be converted.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786