[SOLVED] How to format a number in etext Template

How to format a number in etext Template

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'

Solution

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.