Blog Post

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating