SQL Occurence character Replace issue

Question

I'm working on Oracle's SQL and I would like to have the following results :

4,500,400,40 --> 4 500 400,40

5400200,00   --> 5 400 200,00

1200,988,00  --> 1 200 988,00

I tried to use REGEXP_REPLACE but with no result, so if someone could help me if would be wonderful.

Thanks.

Answer 1

Here's one option:

SQL> with test (col) as
  2    (select '4,500,400,40' from dual union all
  3     select '5400200,00'   from dual union all
  4     select '1200,988,00'  from dual union all
  5     select '28 200 600,5' from dual union all
  6     select '40 500 600'   from dual
  7    )
  8  select col,
  9  to_char(
 10    to_number(  replace(  replace(case when instr(col, ',') = 0 then col ||',00'
 11                                       else col
 12                                  end,
 13                                  ' ', ','
 14                                 ),
 15                          ',', ''
 16                        )
 17             ) / 100,
 18                 'fm999G999G999G990D00',
 19                 'nls_numeric_characters = '', '''
 20                ) result
 21  from test;

COL          RESULT
------------ -------------------
4,500,400,40 4 500 400,40
5400200,00   5 400 200,00
1200,988,00  1 200 988,00
28 200 600,5 2 820 060,05
40 500 600   40 500 600,00

SQL>

What does it do (now that it got more complicated)?

• nested REPLACE:
  • inner checks whether source string contains comma , character; if not, appends ,00 suffix (that's for values like '40 500 600')
  • outer is as before - substitutes commas with an empty string
• to_number converts such a string into a number
• it is divided by 100
• to_char formats it