What are all values ISNUMERIC function finds as TRUE in sql server?

Question

Below part of my query was failing initially:

WHEN ISNUMERIC(npx.nvcAnswer) = 1
            THEN CASE 
            WHEN ABS(CONVERT(DECIMAL(38,2),npx.nvcAnswer)) < 1 
            THEN CONVERT(VARCHAR,CONVERT(DECIMAL(38,2),npx.nvcAnswer)) 
            ELSE npx.nvcAnswer 
            END

Below was the error we were getting:

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to numeric. rootCause=SQLException #1: errorCode: 8114 sqlState: S0005 message: Error converting data type nvarchar to numeric.

I realized that it considers '.' & '-' as NUMERIC too.

So I added "NOT npx.nvcAnswer in( '.' , '-')" to it and it worked.

WHEN ISNUMERIC(npx.nvcAnswer) = 1 AND  NOT npx.nvcAnswer in( '.' , '-')
    THEN CASE 
    WHEN ABS(CONVERT(DECIMAL(38,2),npx.nvcAnswer)) < 1 
    THEN CONVERT(VARCHAR,CONVERT(DECIMAL(38,2),npx.nvcAnswer)) 
    ELSE npx.nvcAnswer 
    END

After some day it started failing again with below error.

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to numeric. rootCause=SQLException #1: errorCode: 8114 sqlState: S0005 message: Error converting data type nvarchar to numeric.

Then I changed below block and it worked: (But I failed to find what values were causing this issue)

OLD:

   WHEN ABS(CONVERT(DECIMAL(38,2),npx.nvcAnswer)) < 1 
    THEN CONVERT(VARCHAR,CONVERT(DECIMAL(38,2),npx.nvcAnswer)) 

NEW:

  WHEN ABS(CONVERT(DECIMAL(38,2),PATINDEX('%[^0-9]%', npx.nvcAnswer))) < 1 
    THEN CONVERT(VARCHAR,CONVERT(DECIMAL(38,2),PATINDEX('%[^0-9]%', npx.nvcAnswer)))

Ask :

What are all values ISNUMERIC function finds as TRUE in sql server?

Answer 1

I will suggest you should your Try_Cast() function instead of using complex logic to check numeric values.

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). Please refer below links for further detail -

https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql