Placing a decimal point just before last two digit of a number-CodePudding

I have a query as below

|| LPAD (TRIM (TO_CHAR (RWTEXPT_STD_AMOUNT, 'FM9999999999999D00')), 15, '0')

its giving the result : 000011545467.00

what i need is : 000000115454.67

i have tried 'FM9999999999999D00' and '999999999999D99' but it gives the same results 000011545467.00

what i need is 000000115454.67

CodePudding user response：

Convert your string value to a number, divide it by 100 and then format it:

SELECT TO_CHAR(
         TO_NUMBER(RWTEXPT_STD_AMOUNT)/100,
         'FM000000000000D00'
       ) AS result
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (RWTEXPT_STD_AMOUNT) AS
SELECT '000000011545467' FROM DUAL;

Outputs:

RESULT
000000115454.67

fiddle

CodePudding user response：

Just do it this way

Select To_Char(14.5, 'FM000000000000D00') "NMBR" From Dual
--
--  NMBR           
--  ----------------
--  000000000014.50

Put any number of leading zeros within the format. Zero means zero and 9 is a placeholder if there is a number present. Letter D is for decimal point character. You can use G for grouping character too.
Regarding decimals - if your column has integer value like 1467 and you know that last two numbers are decimal numbers then just put 1467/100

Select To_Char(1467/100, 'FM000000000000D00') "NMBR" From Dual
--
--  NMBR           
--  ----------------
--  000000000014.67

Regards...