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.