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 (,).
