I am having requirement where customer can enter a number in any format and I need to convert it into valid number format using plsql code dynamically. could you please guide me.

• Test case1# (200.000,00) Formatted number: -200000.00
• Test case2# (200,000.00) Formatted number: -200000.00
• Test Case3# (20000) Formatted number: -20000.00
• Test Case4# 1000000 Formatted number: 1000000.00
• Test Case5# (20,000.00) Formatted number: -20000.00
• Test Case6# 10,000,000 Formatted number: 10000000.00
• Test Case7# 12.5 Formatted number: 12.50
• Test Case8# 12,5 Formatted number: 12.50

Any help is really appreciated. Thanks in advance.

I tried below code However its not working for all scenarios.

SELECT 
    attribute2,
    CASE 
        WHEN REGEXP_LIKE(Number, '^d{1,3}(?:.d{3})*(?:,d{2})?$') THEN 
            TO_NUMBER(REPLACE(Number, '.', ''), '999999.99')
        WHEN REGEXP_LIKE(Number, '^d{1,3}(?:,d{3})*(?:.d{2})?$') THEN 
            TO_NUMBER(REPLACE(Number, ',', ''), '999999.99')
        ELSE
            NULL
    END AS formatted_amount
FROM TEST ;