regexoracle-databaseprecisionfixed-point

Regex for Oracle NUMBER(10,8) field

I have an Oracle database column of data type NUMBER(10,8). I need to validate the input data via regex (in Java) before storing it in tables. As per Oracle's data type, valid values include:

...and so on. Negative values of these cases are also valid.

I can write a regex for one case at a time, f.e. we can check for 1234567891 with one regex. Then with changes in the range, we can write respective regexes for all the possible combinations of the scale.

My sample regex is:

^-?\d{0,2}(?>\.\d{1,8})?$

It checks in this case for 2 digits, a dot, and 8 digits.

Now I want to know: is there any easier way of checking all such values in one regex? One can always use the | operator, but then the total number of such ORs would be equal to the scale part of the data type.

Is there any elegant possible solution? Any pointers, suggestions are welcome!

UPDATE:

After @Andreas pointed out the actual meaning of (10,8), the question does seem to be misguided. Removing the invalid cases from the above mentioned, the valid cases are:

Solution

  • You've misunderstood the meaning of NUMBER(10,8):

    Specify a fixed-point number using the following form:

    NUMBER(p,s)

    where:

    • p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. [...]

    • s is the scale, or the number of digits from the decimal point to the least significant digit. [...]

    It means at maximum 10 significant decimal digits, with 8 digits from (speak: after) the decimal point, i.e. 2.8 only. The scale is not floating. Sure, you can have fewer on each side of the decimal point, but not more than 2 on the left (before) and 8 on the right (after).

    Oracle names this a fixed-point number, and it is very distinct from a floating-point number, which uses the same keyword but without limits, i.e. NUMBER.

    As for Oracle Database number literal format, the format is:

    If you exclude scientific notation, you want a regex of:

    ^[+-]?(?:\d{1,2}(?:\.\d{0,8})?|\.\d{1,8})$