Checking For Numeric Validation

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 ch

Solution 1:

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@myfieldvarchar(20)
set@myfield='.'
if ISNUMERIC(@myfield)=1beginselectCONVERT(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

Solution 2:

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 notlike'%[^0-9]%'

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

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

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]%'andlen(myfield) <= 10and myfield <= '2147483647'

(assuming there are no leading 0s).

Solution 3:

Check it like this instead:

if (IsNumeric(myField))
 begin--does some workend;