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

@ojhawkins - [`ISNUMERIC`](http://technet.microsoft.com/en-us/library/ms186272.aspx) returns an `int` — Martin Smith, Aug 25 '13 at 11:23
You need to show some more relevant code though. The code you have shown us isn't the code that throws the error. We need more context to see what you are actually doing. — Martin Smith, Aug 25 '13 at 11:25
An incorrect assumption by me @Martin Smith http://technet.microsoft.com/en-us/library/ms186272.aspx — ojhawkins, Aug 25 '13 at 12:18

score 3 · Answer 1 · edited May 23 '17 at 12:05

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

score 3 · Answer 2 · answered Aug 25 '13 at 11:34

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

score 1 · Answer 3 · answered Aug 25 '13 at 10:44

1

Check it like this instead:

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

answered Aug 25 '13 at 10:44

Fabian Bigler

10,403
6
47
70

-1 This is not correct TSQL syntax. `IsNumeric` is a function that returns `0` or `1`. It doesn't return a boolean result that can be used like that. – Martin Smith Aug 25 '13 at 11:20

Checking for numeric validation

3 Answers3

Linked