Validating number of digits in a number oracle sql

Question

I want to validate the number of digits of numbers so I can check if they follow the rules provided (9,6);

• no more than 9 digits in total
• max 3 digits before the dot
• max 6 digits after the dot

For example Column A have the following values;

• 123.456434 --> correct
• 12.4535 --> correct
• 13234.3543 --> not correct (to many digits before the dot)
• 13.4757832 --> not correct (to many digits after the dot)

In a previous test I only tested the digits after the dot with the following sql;

select column_a ,case when length(regexp_substr(column_a,'\d+$')) > (9,6) then 'Not OK' else 'OK' end column_a_test from table

However this doesn't seem to work to check the digits before the dot. How can I tweak the sql so that it tests both digits before and after the dot?

Answer 1

Your adapted question with optional decimal point requires some care in the length check.

You can't set it simple optional in the regexp [.]? as this would accept long integers e.g. 1000

Simple use two options in the regexp, one without the decimal point ^[[:digit:]]{0,3}$

and the other with it ^[[:digit:]]{0,3}[.][[:digit:]]{0,6}$

The sample query

select num,
case when regexp_like(num, '(^[[:digit:]]{0,3}$|^[[:digit:]]{0,3}[.][[:digit:]]{0,6}$)' )
          then 'OK' else 'NOT OK'
 end as is_ok
from tab;

NUM        IS_OK 
---------- ------
123.45643  OK    
12.4535    OK    
13234.3543 NOT OK
13.4757832 NOT OK
13,475     NOT OK
38383      NOT OK
100        OK  

Note also that the correct syntax for occurence operator is {m,n} and not {m-n}

Answer 2

If I understand correctly:

select . . . ,
       (case when regexp_like(column_a, '^[0-9]{0-3},[0-9]{0-6}$')
             then 'OK' else 'NOT OK'
        end)

This assumes that the comma is required. If not, use '^[0-9]{0-3}[,]?[0-9]{0-6}$'.