CODEWITHSUNDEEP
sqloracle-databaseplsql
Stefan Dramlic
Stefan Dramlic

Reputation: 69

Number formatting in SQL

I have a number that needs to be formatted like this:

For example, number 1,234,567.89 needs to look like 1.234.567,89.

Is there any way that I can do this with a simple sql function or I have to make my own function?

Upvotes: 0

Views: 171

Answers (2)

hol
hol

Reputation: 8421

You can alternatively also work with the session territory setting.

create table mytest (field1 number);
insert into mytest values (1234567.89);
alter session set NLS_TERRITORY=GERMANY;
select field1, to_char(field1,'9G999G999G999D00') from mytest;
alter session set NLS_TERRITORY=AMERICA;
select field1, to_char(field1,'9G999G999G999D00') from mytest;

Output:

Table created.
1 row created.
Session altered.

    FIELD1 TO_CHAR(FIELD1,'9G999G999G999D00')
---------- ----------------------------------
1234567,89      1.234.567,89                 
1 row selected.
Session altered.

    FIELD1 TO_CHAR(FIELD1,'9G999G999G999D00')
---------- ----------------------------------
1234567.89      1,234,567.89                 
1 row selected.

Upvotes: 1

user330315
user330315

Reputation:

Use to_char() together with the specification that you want to use , as the decimal separator and . for the thousands separator (which is not the default in Oracle) 

select to_char(1234567.89, '9G999G999G999D00', 'NLS_NUMERIC_CHARACTERS = '',.''')
from dual;

Results in: 1.234.567,89

Details about format models: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00211

Details about the to_char() function: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions201.htm#SQLRF51882

Upvotes: 3

Related Questions