oracle replace comma with period and period with comma

How to change comma with period and period with comma e.g. 1,50,000.25 to 1.50.000,25 in开发者_StackOverflow社区 oracle

For numerics these characters - the group separator and the decimal separator - are controlled by the NLS (Globalization) parameters. The defaults are defined by NLS_TERRITORY but we can override those with specific characters through the NLS_NUMERIC_CHARACTERS parameter:

SQL> var n number
SQL> exec :n := 1000000.123

PL/SQL procedure successfully completed.

SQL> select :n from dual
  2  /

         :N
-----------
1000000.123

SQL> select to_char(:n, '9G999G999D999') from dual
  2  /

TO_CHAR(:N,'9G
--------------
 1,000,000.123

SQL> alter session set nls_numeric_characters = ",."
  2  /

Session altered.

SQL> select to_char(:n, '9G999G999D999') from dual
  2  /

TO_CHAR(:N,'9G
--------------
 1.000.000,123

SQL>

The Globalization stuff is covered extensively in the documentation. Find out more.

use replace

you may want to do tihs in a 3-pass process

e.g. swap all ',' into a unique 'safe' character (like ~)

then

swap all '.' into ','

finally

swap all '~' into '.'