It's Dangerous to ISNUMERIC, Take This Try_Convert

There's an issue with some characters in T-SQL when validating numeric values out of raw varchar fields, and it can be easily solved with a function new to SQL Server 2012.

Consider this sample setup, where we are stuck importing values from a varchar data source into an integer data destination, a common task in heterogeneous ETL activities:

create table tablefoo
(id int identity(1,1) not null primary key,
foo varchar(20) not null)
go
--add some valid integers
insert into tablefoo (foo) values
(123),
(456)

--add some dollar values
insert into tablefoo (foo) values
('$123'),
('$456')

--add some alphanumeric strings
insert into tablefoo (foo) values
('abc123'),
('def456')

--add scientific notation
insert into tablefoo (foo) values
('1e234')
go
select * from tablefoo 

/*
id foo
1 123
2 456
3 $123
4 $456
5 abc123
6 def456
7 1e234
*/

Let's try to validate only proper integer values in tablefoo.foo for insertion into a column with a data type bigint.

--this fails! why?
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c

The error message is
Msg 8114, Level 16, State 5, Line 1 Error CONVERTing data type varchar to bigint.
Let's dig into the data case-by-case...

--this succeeds as designed, returning NULL for the alphanumeric value.
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c
where foo = 'abc123'

--This fails because the dollar sign passes the ISNUMERIC check 
-- BUT is not valid for an int field 
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c
where foo = '$123'

--This also fails because 1e234 is valid scientific notation 
-- BUT is not valid for an int field.
select 
      CASE   WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END
from tablefoo c
where foo = '1e234'

Our culprit was the curious behavior of certain characters (in this case, "$" and "e") which is valid for the ISNUMERIC built-in function, commonly used to detect numeric strings.

How to clean this up?

--this returns only integer values properly for the whole table
-- and is much prettier.
select 
 c.foo
, ISNUMERIC(c.foo) 
, TRY_CONVERT(bigint, c.foo )
from tablefoo c

Note the above values of ISNUMERIC and the error-less conversion of the TRY_CONVERT function. So not only is TRY_CONVERT downright sexy with its efficiency and compactness, it can help you avoid errors commonly encountered while using ISNUMERIC with characters like the dollar sign ($), decimal point (.), scientific notation (e) and comma (,).