다음을 통해 공유

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/

Comments

  • Anonymous
    December 30, 2013
    Thanks about this. The details matter.

  • Anonymous
    March 01, 2015
    Thank you, good explaination!!!

  • Anonymous
    June 11, 2015
    Great post....Thanks mate !

  • Anonymous
    July 27, 2015
    I know this is kind of an old post, but just wanted to point out that ISNUMERIC from SQLSERVER returns 1 if the parameter can be parsed to any numeric type, this is different from "the input expression evaluates to a valid numeric data type". Also if you do something like ISNUMERIC('0d123') this will return 1 as well beacuse it can be converted to a float. (Scientific notation). So basically if you are stuck with SQLSERVER 2008 or before you can use a regular expression like this to avoid problems: not @val like '%[^0-9]%'

  • Anonymous
    February 25, 2016
    Fine when necessary ! But be careful with the overload generated by using CLR. Test confirm that it take really longer then system functions.

  • Anonymous
    June 07, 2016
    Try_parse has a issue when trying to parse TRY_PARSE('1,' as NUMERIC(10,2)) IT throws (1.00) which is not expected. I want this to be NULL. Instead use TRY_CAST which will give you expected result.

  • Anonymous
    July 19, 2016
    A small perhaps irrelevant got'ya to an old post, but if you are using try_parse to validate user input to say a stored procedure, additional validation is necessary. Lets assume for argument sake you have a stored procedure that will return the last x # of objects created. @ObjectsToReturn INT being your input parameter. if someone were to pass in null or pass in nothing, and the default went to null (again for arguments sake)SELECT TRY_PARSE(@ObjectsToReturn AS INTEGER) AS ExprResult will give you a hard fail SELECT ISNUMERIC(@ObjectsToReturn) will give you the correct result. long and short you will have pros and cons to each approach. They will both require extra validation be it NOT NULL, COALESCE, or others.