Oracle PL/SQL - Check number formats and masks

After a long time without doing PL/SQL... I need a suggestion from the community, for something that apparently is trivial, but I am a little bit stuck on this.

I am building a load from an CSV file, and there we have a column with the amount. The CSVs come from different suppliers, and each one can send the amount in different formats. So I should reject lines from the CSV with amount that are not in correct number format (999,999,999,999.00), because can be an incorrect amount reported by the supplier and should be fixed.

Coming with the formats 999.999.999,00 or 999999999,99, I can do some treatments through PL/SQL to convert. But I am having problems with values with different formats as 999,9,9,9 or whatever...

I am trying to use common functions (TO_NUMBER, TO_CHAR). But not having much success...

SELECT TO_NUMBER('999,9,9,9') FROM DUAL; or SELECT TO_NUMBER('999,9,9,9','99G990G990G990G990G990D00') FROM DUAL;

The result is ORA-01722: invalid number, and that is brilliant! However, it would reject other formats that seem correct, like 9,999.99

SELECT TO_NUMBER('999,9,9,9','99G999G999G999G999G999D00') FROM DUAL;

Using a format mask, the value 999,9,9,9 is converted to 999999 - and it is not fine. However using the format mask works fine for 9,999.99, for example.

Do you know any other function provided by Oracle that can help solve my issue?

Or any suggestion on how can I do this?

Thank you very much.

Att.,

Guilherme

Solution

You can use a regular expression to determine a valid number or not. Then if valid replace comma with nulls and thee convert that to a number. The queries below use the following regular expression:

'^(\d{1,3})(\,\d{3})*(\.\d{2}|\.?)$'

It breaks down as follows

^(\d{1,3}) --- at beginning of string 1 to 3 digits
(\,\d{3})* --- followed by 0 or more sets of comma followed by 3 digits
(.\d{2}|.?) --- that followed by decimal point followed by 2 digits OR (|) optional decimal point
$ --- end of string

Demo:

with test (num, expected)
   as (select '999,999,999,999.00', 'valid'    from dual union all
       select '999,99,999,999.00',  'invalid'  from dual union all 
       select '999.00',             'valid'    from dual union all 
       select '99',                 'valid'    from dual union all 
       select '9,999.',             'valid'    from dual union all 
       select '9,999..0',           'invalid'  from dual union all         
       select '999,99999,999.00',   'invalid'  from dual  
      )
select num
     , expected
     , case when regexp_like(num,'^(\d{1,3})(\,\d{3})*(\.\d{2}|\.?)$')   
            then to_char(to_number(replace(num,',',null)))
            else 'Not Valid Number'
       end converted
  from test;

In a live setting you would not want the "to_char(to_number ..." structure). This was used for demonstration/testing as both then and else of the case statement must result in same data type. A live version would appear something like:

with test (num)
   as (select '999,999,999,999.00'  from dual union all
       select '999,99,999,999.00'   from dual union all 
       select '999.00'              from dual union all 
       select '99'                  from dual union all 
       select '9,999.'              from dual union all 
       select '9,999..0'            from dual union all         
       select '999,99999,999.00'    from dual  
      )
select to_number(replace(num, ',', null))
  from test
 where regexp_like(num,'^(\d{1,3})(\,\d{3})*(\.\d{2}|\.?)$');