Home » SQL & PL/SQL » SQL & PL/SQL » TO_NUMBER specify the decimal separator within a query. (Oracle 11g)
TO_NUMBER specify the decimal separator within a query. [message #675259] Tue, 19 March 2019 10:04 Go to next message
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.

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?
Re: TO_NUMBER specify the decimal separator within a query. [message #675262 is a reply to message #675259] Tue, 19 March 2019 13:24 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
Please define "does not work".

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


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 Go to previous messageGo to next message
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".
Re: TO_NUMBER specify the decimal separator within a query. [message #675265 is a reply to message #675264] Tue, 19 March 2019 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67375
Registered: March 2007
Location: Nanterre, France, http://...
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:
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 Go to previous messageGo to next message
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

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?
Re: TO_NUMBER specify the decimal separator within a query. [message #675302 is a reply to message #675299] Thu, 21 March 2019 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67375
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

Re: TO_NUMBER specify the decimal separator within a query. [message #675328 is a reply to message #675302] Fri, 22 March 2019 04:21 Go to previous message
cookiemonster
Messages: 13894
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.
Previous Topic: trunc function and inner joins
Next Topic: existing state of package - Invalidated
Goto Forum:
  


Current Time: Thu Oct 01 15:25:05 CDT 2020