19

I need to checking a column where numeric or not in SQL Server 2012.

This my case code.

CASE
    WHEN ISNUMERIC(CUST_TELE) = 1 
      THEN CUST_TELE 
      ELSE NULL 
END AS CUSTOMER_CONTACT_NO

But when the '78603D99' value is reached, it returns 1 which means SQL Server considered this string as numeric.

Why is that?

How to avoid this kind of issues?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
weeraa
  • 1,123
  • 8
  • 23
  • 40
  • 6
    `ISNUMERIC` answers the question that nobody wants to ask "Can this string be converted to *any* of the numeric data types (I don't *care* which types and I don't want you to tell me)?". 78603D99 is a valid string that can be converted to a `float`. – Damien_The_Unbeliever Aug 21 '15 at 09:00
  • 1
    `ISNUMERIC` is a secret Microsoft joke function, made on April 1st, 1999, but someone forgot to remove it for RTM. Then for the release of SQL Server 2005, another MS developer innocently tried to make it work better (see changes to SQL Server 2005 [here](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms178653(v=sql.90)#remarks) and search for 'ISNUMERIC' on the page). See comments [here](https://www.brentozar.com/archive/2018/02/fifteen-things-hate-isnumeric/) on Brent Ozar's great blog. Don't ever use it for any purpose whatsoever. Ever. – Reversed Engineer Aug 01 '19 at 12:17

3 Answers3

25

Unfortunately, the ISNUMERIC() function in SQL Server has many quirks. It's not exactly buggy, but it rarely does what people expect it to when they first use it.

However, since you're using SQL Server 2012 you can use the TRY_PARSE() function which will do what you want.

This returns NULL: SELECT TRY_PARSE('7860D399' AS int)

This returns 7860399 SELECT TRY_PARSE('7860399' AS int)

https://learn.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-ver16

Obviously, this works for datatypes other than INT as well. You say you want to check that a value is numeric, but I think you mean INT.

Andrew Carmichael
  • 3,086
  • 1
  • 22
  • 21
  • Note to OP: Be sure to account for the fact that this returns `NULL` rather than `0` or `FALSE`. This is important if you don't want to accidentally exclude `0`'s from your results. – MatBailie Aug 21 '15 at 09:16
  • Note that this return true for negative numbers and limits the length of the string. – Gordon Linoff Aug 21 '15 at 11:03
  • you can also use `TRY_CAST`. (as `TRY_PARSE` is not a SQL Native function, rather it is a .NET run-time dependent function. Also, it may be stronger for special data types like as date-time but it has probably more performance overhead) – S.Serpooshan Dec 30 '18 at 07:31
7

Although try_convert() or try_parse() works for a built-in type, it might not do exactly what you want. For instance, it might allow decimal points, negative signs, and limit the length of digits.

Also, isnumeric() is going to recognize negative numbers, decimals, and exponential notation.

If you want to test a string only for digits, then you can use not like logic:

(CASE WHEN CUST_TELE NOT LIKE '%[^0-9]%'
      THEN CUST_TELE 
 END) AS CUSTOMER_CONTACT_NO

This simply says that CUST_TELE contains no characters that are not digits.

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

Nothing substantive to add but a couple warnings.

1) ISNUMERIC() won't catch blanks but they will break numeric conversions.

2) If there is a single non-numeric character in the field and you use REPLACE to get rid of it you still need to handle the blank (usually with a CASE statement).

For instance if the field contains a single '-' character and you use this:

cast(REPLACE(myField, '-', '') as decimal(20,4)) myNumField

it will fail and you'll need to use something like this:

CASE WHEN myField IN ('','-') THEN NULL ELSE cast(REPLACE(myField, '-', '') as decimal(20,4)) END myNumField
screechOwl
  • 27,310
  • 61
  • 158
  • 267