How to format a number in etext Template

Question

I have a requirement in my BIP Report where if the value is '163.8', the expected result should be '00000163.800'

I'm using pipe delimited etext template.

I've tried the following commands but nothing worked:

Number, ###.##
TO_NUMBER(MyStringName,'99999999.999')
TO_NUMBER(MyStringName,'00000000.000')
TO_NUMBER(MyStringName,'########.###')
FORMAT_NUMBER(MyStringName,'99999999.999')

MyString = '163.8' Expected Output = '00000163.800'

Answer 1

If you're converting a string (as you said), you could

• first convert it to a number, and then back to character, using appropriate format mask (result_1)
• apply nested RPAD & LPAD function (result_2)

For example:

SQL> with test (col) as
  2    (select '163,8' from dual)
  3  select
  4    to_char(to_number(col), '0000000D000') result_1,
  5    lpad(rpad(col, length(col) + 2, '0'), 11, 0) result_2
  6  from test;

RESULT_1     RESULT_2
------------ -----------
 0000163,800 0000163,800

SQL>

See if any of these helps.