How to Determine if value is Numeric by using ISNumeric and Try_Convert Function in SQL Server - TSQL Tutorial

ISNUMERIC( ) function is provided to us in SQL Server to check if the expression is valid numeric type or not. As per Microsoft it should work with Integers and Decimal data types and if data is valid integer or decimal, ISNumeric() should return us 1 else 0. But ISNUMERIC() does not work as expected with some of values specially when we have "-" or "d" in value and have two numbers after "d" such as 123d22, it still return us 1. Also if we have data in money format $XXXXX e.g $2000, It returns us 1.


In SQL Server 2012. Microsoft introduced new function call Try_Convert( ). You can use try_convert function to convert to required data type and if it is not able to convert then it will return Null as output. As you will see below, I did some experiment and found out that Try_Convert will produce 0 for "-" when we try to convert to Int, That should not be happening as "-" is symbol not Integer. But when I try to convert "-" to decimal, Try_Convert produced Null output.

Take a look in below results and keep in mind the outputs when you have to evaluate expression to Numeric or find out if expression is Numeric or Not.

USE TestDB
GO
CREATE TABLE #Customer (ColName VARCHAR(100))
GO
--Insert some Sample Data
INSERT INTO #Customer 
Select '1-2'   Union
Select '54f54' Union
Select '123'   Union 
Select '123.09' Union
Select '-' Union
Select '24d09' Union
Select '$200' Union
Select 'TestData' Union
Select 'Street 123' Union
Select '123$' Union
Select '$234$' Union
Select '1.2' Union
Select '1' Union
Select '234-4' Union 
Select '_'
    
--Use ISNUMERIC AND TRY_Convert Functions to Check if Given Records Are Numeric OR NOT
SELECT ColName
    ,ISNUMERIC(ColName) AS ISNUMERIC_Function_Output
    ,try_convert(DECIMAL(18, 3), ColName) AS DecimalConversion
    ,TRY_CONVERT(INT, ColName) AS INTConversion
    ,CASE 
        WHEN ColName='-' THEN 'Not Numeric'
        WHEN try_convert(DECIMAL(18, 3), ColName) IS NOT NULL
            THEN 'NUMERIC'
        WHEN try_convert(INT, ColName) IS NOT NULL
            THEN 'NUMERIC'
        ELSE 'Not Numeric'
        END AS IsNumericOrNot
FROM #Customer

GO
Drop table #customer 

Fig 1: Using ISNUMERIC()  or Try_Convert Function to evaluate if Expression is Numeric

SQL Server Video Tutorial: Learn Details of ISNUMERIC AND Try_Convert Function