ISNUMERIC()? No, thank you!

Can we trust ISNUMERIC() function when checking if it is safe cast string value to numeric data type? Definitely not, because it´s more or less unusable for this purpose, because we can´t be sure what is hidden in string to be converted. And trust me: there is lot of surprises prepared for us.

Why to start with something simple? There is one really funny example from production scenario where finding the issue consumed more then the lunch time.

We will create dbo.SampleTable with VARCHAR column and insert into it numeric values with small cosmetics improvement for 3rd row:

CREATE TABLE dbo.SampleTable
(
    Value VARCHAR(8)
)
GO

INSERT dbo.SampleTable
    SELECT '1' UNION ALL
    SELECT '2' UNION ALL
    SELECT '3' + CHAR(160) UNION ALL -- ASCI 160 = nbsp;
    SELECT '4'
GO

SELECT * FROM dbo.SampleTable
GO

In next step we will try to use ISNUMERIC() function to approve, if all the values in dbo.SampleTable are numbers and we can later cast them to INT:

SELECT Value, ISNUMERIC(Value) IsNumber
FROM dbo.SampleTable
GO

All the values are marked as numbers so we can convert them to INT datatype. Let´s  try:

SELECT VALUE, CAST(Value AS INT)
FROM dbo.SampleTable
GO

Not an expected result for sure. CHAR(160) hidden on row 3 is ignored by ISNUMERIC(), but later it´s causing error when casting to INT data type. But don´t worry: there is more, because all of following characters are signed by ISNUMERIC() as valid numbers:

SELECT '+' Value, ISNUMERIC('+') IsNumber UNION ALL 
SELECT '-' Value, ISNUMERIC('-') IsNumber UNION ALL 
SELECT '\' Value, ISNUMERIC('\') IsNumber UNION ALL 
SELECT '$' Value, ISNUMERIC('$') IsNumber UNION ALL 
SELECT '.' Value, ISNUMERIC('.') IsNumber UNION ALL 
SELECT ',' Value, ISNUMERIC(',') IsNumber
GO

Only values on rows 1, 2 (+, -) can be converted to INT without conversion error with output numeric value = 0.

It´s more then obvious that we can´t use ISNUMERIC() as safe check, if we can convert string values to numeric data type without exceptions.

Luckily there is an easy solution starting with SQL Server 2012: TRY_PARSE() and TRY_CAST() functions.

We will insert all our test values into dbo.SampleTable and then test both functions for their output:

INSERT INTO dbo.[SampleTable] ( [Value] )
	SELECT '+' UNION ALL 
	SELECT '-' UNION ALL 
	SELECT '\' UNION ALL 
	SELECT '$' UNION ALL 
	SELECT '.' UNION ALL 
	SELECT ',' UNION ALL
	SELECT '12345' 
GO

SELECT Value, TRY_CAST(value AS INT), TRY_PARSE(Value AS INT)
FROM dbo.[SampleTable]
GO

All values are converted with TRY_CAST(), TRY_PARSE() functions as expected, including hidden CHAR(160) on row 3. The only one difference is for +,- signs, that are converted to 0 with TRY_CAST() and to NULL with TRY_PARSE().

The biggest difference between TRY_CAST() and TRY_PARSE() is in internal implementation: TRY_CAST() is native code function whereas TRY_PARSE() relies on CLR code and has performance overhead as described in this article.