Checking for numeric validation

Question

Hi I have a value that I want to make sure it is only numeric in stored procedure. I have following code

 if (IsNumeric(myField) =0)
 begin
   --does some work
 end;

It should check myField if the number is non numeric it does work if not it just continues. For some reason above code is throwing error. Any idea how to fix that. Error is that exception was thrown varchar to int. Please let me know Thanks

Answer 1

Check it like this instead:

if (IsNumeric(myField))
 begin
   --does some work
 end;

Answer 2

Your expression is valid, I suspect that you are getting a value that is considered a numeric by the function, but cannot be converted to an integer. Try the following...

declare @myfield varchar(20)
set @myfield='.'
if ISNUMERIC(@myfield)=1
begin
    select CONVERT(int,@myField)
end

The convert statement will blow up with the error your are reporting...

Check this question out: T-sql - determine if value is integer

Some more examples of "Numeric" values that cannot be converted to an integer

select '1.e0',ISNUMERIC('1.e0') as IsNum  
union
select '.',ISNUMERIC('.') as IsNum  
union
select '12.31',ISNUMERIC('12.31') as IsNum  

Add a select convert(int,myField) in the begin/end to see the actual field value causing the error to occur

Answer 3

As others have pointed out, isnumeric() will return 1 on occasions where the string cannot be converted.

The following tests for a positive integer:

where myfield not like '%[^0-9]%'

The following tests for a positive integer or floating point number:

where myfield not like  '%[^0-9.]%' and myfield not like '%.%.%'

As a note, none of these methods test for numeric overflow. You could test for that with something like:

where myfield not like '%[^0-9]%' and len(myfield) <= 10 and myfield <= '2147483647'

(assuming there are no leading 0s).