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.
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 |
|
EdStevens
Messages: 1376 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 #675265 is a reply to message #675264] |
Tue, 19 March 2019 13:56 |
|
Michel Cadot
Messages: 68653 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 #675302 is a reply to message #675299] |
Thu, 21 March 2019 12:17 |
|
Michel Cadot
Messages: 68653 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 |
cookiemonster
Messages: 13923 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.
|
|
|