0

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 ;
Improve this question
11
  • 3
    So, you want to accept the comma as decimal separator and dot as thousand separator, but also dot as decimal separator and comma as thousand separator. Which leads us to: Does 1.000 equal 1 or 1000? Does 1,000 equal 1 or 1000? Commented Sep 6, 2024 at 20:43
  • 1
    You tagged your request with plsql. But your approach uses SQL, not PL/SQL. Are you looking for a PL/SQL solution (i.e. a function in Oracle's programming language PL/SQL) or for a SQL solution for an Oracle database? Commented Sep 6, 2024 at 20:45
  • Hi Thorsten We have multiple customers and each customer will give input values different. we need to handle it dynamically. PFB the details for that- Test case1# User Input Value: (200.000,00) Converted Input Value: -200000 Formatted number: -200000.00 Test case2# User Input Value: (200,000.00) Converted Input Value: :-200000 Formatted number: -200000.00 Test Case3# User Input Value: (20000) Converted Input Value: -20000 Formatted number: -20000.00 Test Case4# User Input Value: 1000000 Converted Input Value: 1000000 Formatted number: 1000000.00 Commented Sep 9, 2024 at 17:08
  • Test Case5# User Input Value: (20,000.00) Converted Input Value: -20000 Formatted number: -20000.00 Test Case6# User Input Value: 10,000,000 Converted Input Value: 10000000 Formatted number: 10000000.00 Test Case7# User Input Value: 12.5 Converted Input Value: 12.5 Formatted number: 12.50 Test Case8# User Input Value: 12,5 Converted Input Value: 12.5 Formatted number: 12.50 Commented Sep 9, 2024 at 17:09
  • You don't have to repeat what you have already written in your request. Just answer the two questions. Does 1.000 equal 1 or 1000? Does 1,000 equal 1 or 1000? Commented Sep 9, 2024 at 17:24
Related questions
1304
How to print a number using commas as thousands separators
0
Oracle PL/SQL - Check number formats and masks
208
Write a number with two decimal places SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.