Home » SQL & PL/SQL » SQL & PL/SQL » to_char(0.1) result is ,1 but must be 0,1 (Oracle 10g)
to_char(0.1) result is ,1 but must be 0,1 [message #399775] Fri, 24 April 2009 00:46 Go to next message
littleant
Messages: 3
Registered: April 2009
Location: Estonia
Junior Member
In Estonia is float numbers format allways with leading zero ( 0,1 ). My NLS settings are:
NLS_TERRITORY = ESTONIA
NLS_LANGUAGE = ESTONIAN

but float numbers are converted to text without leading zero.

select to_char(0.1) from dual;
result: ,1

This is absurd when I need to insert number format everywhere, when I'm using my territory NLS.

so, how can I set the default number conversion format?
Re: to_char(0.1) result is ,1 but must be 0,1 [message #399776 is a reply to message #399775] Fri, 24 April 2009 01:00 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
You need to issue a format mask.
Check here for the correct format.
Re: to_char(0.1) result is ,1 but must be 0,1 [message #399778 is a reply to message #399776] Fri, 24 April 2009 01:13 Go to previous messageGo to next message
littleant
Messages: 3
Registered: April 2009
Location: Estonia
Junior Member
Do you mean that I need allways put format into to_char?

to_char(0.1, '99,999')

This is absurd. I want write SQL's like this:

select 'Price is ' || UNIT_PRICE || ' EURO '
from PRICES
Re: to_char(0.1) result is ,1 but must be 0,1 [message #399783 is a reply to message #399778] Fri, 24 April 2009 01:25 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
This is not absurd.
SQL is a language to extract data from a database, so your query fetches what's in the db. In the database, your value is stored as a number.
Now it's up to your front-end application to display the data fetched through SQL in the way you desire.
Re: to_char(0.1) result is ,1 but must be 0,1 [message #399788 is a reply to message #399783] Fri, 24 April 2009 01:44 Go to previous messageGo to next message
littleant
Messages: 3
Registered: April 2009
Location: Estonia
Junior Member
So there is no possibility to set default number format. I must declare constant in PL/SQL and put it every to_char() function manually.
Re: to_char(0.1) result is ,1 but must be 0,1 [message #399790 is a reply to message #399788] Fri, 24 April 2009 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> set numformat 99999990D00
SQL> select .5 from dual;
          .5
------------
        0.50

1 row selected.

Regards
Michel
Re: to_char(0.1) result is ,1 but must be 0,1 [message #399837 is a reply to message #399788] Fri, 24 April 2009 04:36 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Depends on your front-end tool.
Previous Topic: Converting inline views to temp tables
Next Topic: Help me to solve error ORA-00904 (merged 3)
Goto Forum:
  


Current Time: Fri Feb 14 09:47:18 CST 2025