Validation for Integer value

Question

I want to validate one column AMOUNT Varchar(2) which should contains only integer value. I am using below logic :

 IF regexp_like(amount,'^[0-9]+') THEN
        X_STATUS:=true;
           else X_STATUS:=false;
            X_REMARKS:='amount SHOULD BE INTEGER';
            RETURN;
        END IF;

But it doesn't work , Kindly help me for the optimal solution: Thank You

What do you mean by `it doesn't work`? Is there an error message? Do you get un-expected results? does the code invalidate legitimate digits or vice-versa? — Ahmad, Sep 10 '20 at 10:47

score 4 · Accepted Answer · answered Sep 10 '20 at 10:42

This is your code:

regexp_like(amount,'^[0-9]+')

It checks if amount starts with a digit - but other characters may be anything. Typically, it would allow something like '1A', '2$' or 3%'.

Consider:

regexp_like(amount,'^[0-9]+$')

$ represents the end of the string in the regex, so this means: all characters must be digits.

If you want to allow the empty string as well (which the above expression does not do), you can change quantifier + to *.

score 3 · Answer 2 · answered Sep 10 '20 at 10:42

You need an anchor for the end of the string:

IF regexp_like(amount, '^[0-9]+$')

This matches all characters in the string.

If you don't want leading zeros either, then:

IF regexp_like(amount, '^([1-9][0-9]*|0)$')

And this assumes that you don't want negative numbers. If you do:

IF regexp_like(amount, '^(-?[1-9][0-9]*|0)$')

Validation for Integer value

2 Answers2