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
