Home » SQL & PL/SQL » SQL & PL/SQL » Two digit decimal
Two digit decimal [message #677926] Wed, 23 October 2019 20:09 Go to next message
Satya.tanmay
Messages: 3
Registered: April 2017
Junior Member
Requirement
Need result in 8digit,(comma not dot) then 2decimal digit i.e. 12345678,90 or 00001234,50 (if value is less than 8 digit then lpad with 0)

I am using query as
select LPAD(replace(to_number(to_char(1234.7,'S999999990D99')),'.',','),11,'0') from dual

I am getting result as : 000001234,7 but i need result as 000001234,70
i.e. 0 at the right is missing.

How can get result as 000001234,70 and what should be my query.
Re: Two digit decimal [message #677927 is a reply to message #677926] Wed, 23 October 2019 20:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Satya.tanmay wrote on Wed, 23 October 2019 18:09
Requirement
Need result in 8digit,(comma not dot) then 2decimal digit i.e. 12345678,90 or 00001234,50 (if value is less than 8 digit then lpad with 0)

I am using query as
select LPAD(replace(to_number(to_char(1234.7,'S999999990D99')),'.',','),11,'0') from dual

I am getting result as : 000001234,7 but i need result as 000001234,70
i.e. 0 at the right is missing.

How can get result as 000001234,70 and what should be my query.
use TO_CHAR to obtain what you desire
Re: Two digit decimal [message #677928 is a reply to message #677926] Thu, 24 October 2019 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(1234.7,'000000000D00') from dual;
TO_CHAR(1234.
-------------
 000001234.70
Re: Two digit decimal [message #677932 is a reply to message #677926] Thu, 24 October 2019 06:34 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
For using a comma instead of a period (dot),
See NLS_NUMERIC_CHARACTERS
and NLS_TERRITORY
Re: Two digit decimal [message #677934 is a reply to message #677932] Thu, 24 October 2019 07:01 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Also asked here: https://community.oracle.com/thread/4297965
Re: Two digit decimal [message #677935 is a reply to message #677928] Thu, 24 October 2019 07:53 Go to previous messageGo to next message
Satya.tanmay
Messages: 3
Registered: April 2017
Junior Member
I need ,(comma) and not .(dot). I tried to_char options but not getting results.

Please post the correct query that will give me the desired result.
Re: Two digit decimal [message #677936 is a reply to message #677926] Thu, 24 October 2019 08:46 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
1. You want all two fractional digits, so you must use 00 not 99.
2. You are replacing D (fractional part separator) with comma so why not simply use third to_char parameter?
3. LPAD makes no sense. You will end up with zeros followed by minus followed by value for negative numbers:

SQL> select LPAD(replace(to_number(to_char(-1234.7,'S999999990D99')),'.',','),11,'0') from dual
  2  /

LPAD(REPLACE(TO_NUMBER(TO_CHAR(-1234.7,'S999
--------------------------------------------
0000-1234,7

SQL> 

4. That to_number negates all work you did converting to string.

Use:

SQL> select to_char(1234.7,'S00000000D00','nls_numeric_characters='',.''') from dual
  2  /

TO_CHAR(1234
------------
+00001234,70

SQL> select to_char(-1234.7,'S00000000D00','nls_numeric_characters='',.''') from dual
  2  /

TO_CHAR(-123
------------
-00001234,70

SQL> 

Replace S with FM if you don't want to display + for non negative numbers.

SY.
Previous Topic: Exist Value in a table
Next Topic: Cursor throwing error while passing to dbms_stats.gather_schema_stats
Goto Forum:
  


Current Time: Thu Mar 28 11:44:29 CDT 2024