CODEWITHSUNDEEP
sqloracle-database
Nutan
Nutan

Reputation: 786

Number format in SQL

I have a number for example 39.46 and i want it to convert in ,format i.e it should look like 39,46 in SQL

is there any function to convert decimal amount in , separated format ?

Upvotes: 0

Views: 981

Answers (2)

Martin Drautzburg
Martin Drautzburg

Reputation: 5253

There is a NLS setting NLS_NUMERIC_CHARACTERS, where you can set "," as decimal separator and "." to separate thousands. They are typically automatically set when you set your locale. You obviously use an English locale.

See here.

This assumes you have actual numbers in your DB and not strings which look like numbers. For strings you may want to use sting conversion operations as described by Thomas G.

Upvotes: 2

user5683823
user5683823

Reputation:

Below I demonstrate how to use the number format and the nls_numeric_characters parameter, both for a numeric input and a string input. Notice the d (or D) in the format model, it says "use whatever the appropriate decimal separator is."

SQL> select to_char(93.23, '999d99', 'nls_numeric_characters='',.''') from dual;

TO_CHAR
-------
  93,23

1 row selected.

Elapsed: 00:00:00.14
SQL> select to_char(to_number('93.23'), '999d99', 'nls_numeric_characters='',.''') 
                                                                            from dual;

TO_CHAR
-------
  93,23

1 row selected.

Upvotes: 0

Related Questions