Systems Engineering and RDBMS

The ISNUMERIC() and LIKE clause gotchas

Posted by decipherinfosys on June 17, 2008

This is something that we have seen in a lot of code at many different places. People use the isnumeric() function of SQL Server without realizing some of the issues that this function does not address and then when things do not work the way they expected, it becomes Microsoft’s fault🙂

Here are some examples to help illustrate the issues with isnumeric() and LIKE clause usage that one should be aware of:

IsNumeric():

set nocount on
go
drop table test
create table test (col1 nvarchar(10))
insert into test values (‘1’)
insert into test values (‘.’)
insert into test values (‘+’)
insert into test values (‘-‘)
insert into test values (‘abcd’)
insert into test values (‘0001’)
insert into test values (‘ ‘)
insert into test values (‘0.25’)
insert into test values (‘$0.25’)
insert into test values (CHAR(9)) /*Tab*/
insert into test values (CHAR(10)) /*Line Feed*/
go

select isnumeric(col1) as col1_numeric_fn, col1 from test

col1_numeric_fn col1
--------------- ----------
1               1
1               .
1               +
1               -
0               abcd
1               0001
0
1               0.25
1               $0.25
1
1

Any surprises for anyone here? Note that “.”, “+”, “-” and “$0.25” return a 1 for the function.

There are better ways of weeding out such values…if you do not have decimals stored within the string, then you can just do:

select case when col1 not like ‘%[^0-9]%’ then 1 else 0 end as is_numeric, col1 from test

is_numeric  col1
----------- ----------
1           1
0           .
0           +
0           -
0           abcd
1           0001
0
0           0.25
0           $0.25
0
0

If you do have decimals also stored in your string, then one can write up a simple UDF (User Defined Function) to take care of all scenarios and use it.

LIKE Clause:

Another gotcha that we would like to point out is with the comparison of non-unicode string with trailing spaces with a unicode string:

select case when ‘test ‘ like N’test’ then ‘matches’ else ‘does not match’ end

————–
does not match

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: