Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012

I was working on a legacy T-SQL script written initially on SQL Server 2005 and I was facing an unexpected behavior. The code was giving me unexpected records, I tried to dig into it and found that ISNUMERIC() function applied to a column was giving me extra records with value like "," (comma) & "." (period) .

=> So, to validate it I executed following code and found that ISNUMERIC() function also passes these characters as numbers:

 SELECT 
 ISNUMERIC('123') as '123'
 ,ISNUMERIC('.') as '.' --Period
 ,ISNUMERIC(',') as ',' --Comma

Function ISNUMERIC() returns "1" when the input expression evaluates to a valid numeric data type; otherwise it returns "0". But the above query will return value "1" for all 3 column values, validating them as numeric values, but that's not correct for last 2 columns.

=>  And not only this, ISNUMERIC() function treats few more characters as numeric, like: - (minus), + (plus), $ (dollar), \ (back slash), check this:

 SELECT 
 ISNUMERIC('123') as '123'
 ,ISNUMERIC('abc') as 'abc'
 ,ISNUMERIC('-') as '-'
 ,ISNUMERIC('+') as '+'
 ,ISNUMERIC('$') as '$'
 ,ISNUMERIC('.') as '.'
 ,ISNUMERIC(',') as ','
 ,ISNUMERIC('\') as '\'

This will return "0" for second column containing value "abc", and value "1" for rest of the column values.

So, you will need to be very careful while using ISNUMERIC() function and have to consider all these possible validations on your T-SQL logic.

- OR -

Switch to new TRY_PARSE() function introduced in SQL Server 2012.

=> The TRY_PARSE() function returns the result of an expression, translated to the requested Data-Type, or NULL if the Cast fails. Let's check how TRY_PARSE() validates above character values as numeric:

 SELECT 
 TRY_PARSE('123' as int) as '123'
 ,TRY_PARSE('abc' as int) as 'abc'
 ,TRY_PARSE('-' as int) as '-'
 ,TRY_PARSE('+' as int) as '+'
 ,TRY_PARSE('$' as int) as '$'
 ,TRY_PARSE('.' as int) as '.'
 ,TRY_PARSE(',' as int) as ','
 ,TRY_PARSE('\' as int) as '\'

So, the above query gives me expected results by validating first column value as numeric and rest as invalid and returns NULL for those.

=> TRY_PARSE() can be used with other NUMERIC & DATETIME data-types for validation, like:

 SELECT 
 TRY_PARSE('123' as int) as '123'
 ,TRY_PARSE('123.0' as float) as '123.0'
 ,TRY_PARSE('123.1' as decimal(4,1)) as '123.1'
 ,TRY_PARSE('$123.55' as money) as '$123.55'
 ,TRY_PARSE('2013/09/20' as datetime) as '2013/09/20'
 

... will give expected results :)

Source: from my personal blog SQLwithManoj: https://sqlwithmanoj.wordpress.com/2013/09/20/use-new-try_parse-instead-of-isnumeric-sql-server-2012/