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: http://sqlwithmanoj.wordpress.com/2013/09/20/use-new-try_parse-instead-of-isnumeric-sql-server-2012/

Comments (6)

  1. gotqn says:

    Thanks about this. The details matter.

  2. Shahzad says:

    Thank you, good explaination!!!

  3. Chanaka says:

    Great post….Thanks mate !

  4. Carlos Aponte says:

    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]%'

  5. E_Geremia says:

    Fine when necessary !

    But be careful with the overload generated by using CLR.

    Test confirm that it take really longer then system functions.

  6. Vinay says:

    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.

  7. JT says:

    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.

Skip to main content