Two digit decimal [message #677926] |
Wed, 23 October 2019 20:09 |
|
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 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Satya.tanmay wrote on Wed, 23 October 2019 18:09Requirement
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 #677936 is a reply to message #677926] |
Thu, 24 October 2019 08:46 |
Solomon Yakobson
Messages: 3275 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.
|
|
|