SQL Server's ISNUMERIC function

Question

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?

Answer 1

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://msdn.microsoft.com/en-us/library/hh213126.aspx

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.

Answer 2

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.