OraFAQ Forum: SQL & PL/SQL » TO_NUMBER specify the decimal separator within a query.

Home » SQL & PL/SQL » SQL & PL/SQL » TO_NUMBER specify the decimal separator within a query. (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TO_NUMBER specify the decimal separator within a query. [message #675259]

Tue, 19 March 2019 10:04

Diego G.
Messages: 8
Registered: June 2017

Junior Member

You can specify the decimal separator, with the TO_NUMBER function within a query. Without having to alter the session or configure the parameter in the system.

Code: [Select all] [Show/ hide]

SELECT TO_NUMBER('-AusDollars100','L9G999D99',
   ' NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY            = ''AusDollars''
   ') "Amount"
     FROM DUAL;

Try this way but it does not work.

Can it be solved?

Report message to a moderator

Re: TO_NUMBER specify the decimal separator within a query. [message #675262 is a reply to message #675259]

Tue, 19 March 2019 13:24

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Please define "does not work".

Works fine for me, copied straight from the SQL Reference manual:

Code: [Select all] [Show/ hide]

SQL> SELECT TO_NUMBER('-AusDollars100','L9G999D99',
  2     ' NLS_NUMERIC_CHARACTERS = '',.''
  3       NLS_CURRENCY            = ''AusDollars''
  4     ') "Amount"
  5       FROM DUAL;

    Amount
----------
      -100

SQL>

We cannot debug code that we cannot see. Show us the transcript of your sqlplus session where "it does not work", exactly like I have shown a transcript of a session where it DOES work.

[Updated on: Tue, 19 March 2019 13:25]

Report message to a moderator

Re: TO_NUMBER specify the decimal separator within a query. [message #675264 is a reply to message #675262]

Tue, 19 March 2019 13:46

Diego G.
Messages: 8
Registered: June 2017

Junior Member

Sorry, the google translator is bad

The query with this entry AusDollars1.234,56 does not convert me to -1234.56. It is solved using the statement (alter session set NLS_NUMERIC_CHARACTERS = ".,"). And what I need is to use the conversion inside the query without using "Alter session".

Report message to a moderator

Re: TO_NUMBER specify the decimal separator within a query. [message #675265 is a reply to message #675264]

Tue, 19 March 2019 13:56

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

The query with this entry AusDollars1.234,56 does not convert me to -1234.56

You have to then use TO_CHAR if you want to change the separators at display time:

Code: [Select all] [Show/ hide]

SQL> alter session set NLS_NUMERIC_CHARACTERS = "!?";

Session altered.

SQL> SELECT TO_NUMBER('AusDollars1.234,56','L9G999D99',
  2     ' NLS_NUMERIC_CHARACTERS = '',.''
  3       NLS_CURRENCY            = ''AusDollars''
  4     ') "Amount"
  5       FROM DUAL
  6  /
    Amount
----------
   1234!56

SQL> SELECT TO_CHAR(
  2         TO_NUMBER('AusDollars1.234,56','L9G999D99',
  3  ' NLS_NUMERIC_CHARACTERS = '',.''
  4    NLS_CURRENCY            = ''AusDollars''
  5  '), '9999D99', 'NLS_NUMERIC_CHARACTERS = ''.,''') "Amount"
  6  FROM DUAL
  7  /
Amount
--------
 1234.56

[Updated on: Tue, 19 March 2019 13:57]

Report message to a moderator

Re: TO_NUMBER specify the decimal separator within a query. [message #675299 is a reply to message #675265]

Thu, 21 March 2019 11:15

Diego G.
Messages: 8
Registered: June 2017

Junior Member

Thanks for helping.

I need to change the decimal separator from (comma) to (point) inside a "SELECT" query

Code: [Select all] [Show/ hide]

select TO_NUMBER('10,35','999G990D00','nls_numeric_characters=''.,''') amount 
from dual; 

--------------
ORA-01722: invalid number

I do not want to use the sentence "ALTER SESSION". It's possible?

Report message to a moderator

Re: TO_NUMBER specify the decimal separator within a query. [message #675302 is a reply to message #675299]

Thu, 21 March 2019 12:17

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

SQL> select to_char(
  2           TO_NUMBER('10,35','999G990D00','nls_numeric_characters='',.'''),
  3           '999G990D00','nls_numeric_characters=''.,''')
  4  from dual;
TO_CHAR(TO_
-----------
      10.35

1 row selected.

Don't confuse numbers and how you see numbers.
nls_numeric_characters are related to the string during conversion (from/to) and has nothing to do with the number itself.

Report message to a moderator

Re: TO_NUMBER specify the decimal separator within a query. [message #675328 is a reply to message #675302]

Fri, 22 March 2019 04:21

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

To add to what Michel said:
Numbers are stored one way, and that way doesn't even include thousand separators since oracle doesn't need them.
When you want a number formatted a certain way for display purposes you need to convert it to a string and apply the format you want at that point.
The same is true for dates.

Report message to a moderator

Previous Topic:	trunc function and inner joins
Next Topic:	existing state of package - Invalidated

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 04:54:12 CDT 2025