Oracle sql to change number format

Question

I need to replace number format with dots and commas to simple decimal number.

Ex: if string is, 25.25.25,00, it should become 252525.00

How can this be achieved.

docs.oracle.com/cd/B19306_01/server.102/b14200/functions134.htm — OldProgrammer
– OldProgrammer, Commented Apr 1, 2021 at 18:21
@OldProgrammer - the link is to the wrong function. The right function for this type of multiple, simultaneous replacement is translate. — user5683823
– user5683823, Commented Apr 1, 2021 at 19:10

Barbaros Özhan · Accepted Answer · 2021-04-01 19:09:55Z

1

You can either consecutively apply REPLACE() functions as

REPLACE(REPLACE('25.25.25,00','.'),',','.')

or

apply first TO_NUMBER() and then TO_CHAR() function as

TO_CHAR(
        TO_NUMBER('25.25.25,00','999999999D99', 'NLS_NUMERIC_CHARACTERS='',.'''),
       'fm999999999D00'
       )

presuming scale has at most two digits for all values that you have for the second case.

answered Apr 1, 2021 at 18:38

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2021-04-01 19:07:37Z

1

Use the translate function:

select translate('25.25.25,00', ',.', '.') as new_str
from   dual
;

NEW_STR  
---------
252525.00

This translates "comma" to "period", and "period" to nothing (since the third argument is shorter than the second) - meaning it is simply discarded.

answered Apr 1, 2021 at 19:07

user5683823