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:
Let's try to validate only proper integer values in tablefoo.foo for insertion into a column with a data type bigint.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 */
The error message is--this fails! why? select CASE WHEN ISNUMERIC(c.foo) = 1 THEN CONVERT(bigint, c.foo ) ELSE NULL END from tablefoo c
Msg 8114, Level 16, State 5, Line 1 Error CONVERTing data type varchar to bigint.
Let's dig into the data case-by-case...
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.--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'
How to clean this up?
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 (,).--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
What is the output of the final select statement using try_convert?
foo (No column name) (No column name)
123 1 123
456 1 456
$123 1 NULL
$456 1 NULL
abc123 0 NULL
def456 0 NULL
123.00 1 NULL
456.00 1 NULL
123,000 1 NULL
1e4 1 NULL
1e234 1 NULL
Well done. The only exception that I take is in the title. For the informed user, ISNUMERIC isn't "dangerous" to use. It's just that uniformed user don't know what it actually does and continue to improperly try to use it as an IsAllDigits function.
That, notwithstanding, thank you for taking the time to publish this informative post.
Thanks Jeff! FWIW the title is a play on the Legend of Zelda game. A little derivative, I know.
Post a Comment