0

I have a data column with varchar(19,4). How do I show it in proper decimal format e.g I want to show 1000000.0000 instead of 1000000 only (need it in specific format for file processing by some third paty). Currently I used TO_CHAR(columnName,'9999999999999999999.9999') and it displays in expected format but I wanted to know if there is a better way rather than writing 9 19 times !

bloomwood
  • 31
  • 2
  • 6
  • 1
    Take a look on Oracle documentation - http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm as the first step. –  Feb 17 '15 at 11:15
  • VARCHAR(19, 4) - as far as I'm aware this is not a valid declaration in Oracle. Oracle 11.1 refuses to accept it as either a column definition or a PL/SQL variable declaration. Nor does it appear to be a valid declaration in other databases, e.g. SQL Server or MySQL. ??? – Bob Jarvis - Слава Україні Feb 17 '15 at 12:21
  • `TO_CHAR(columnName,'9.9999')` should do the same. – Wernfried Domscheit Feb 17 '15 at 14:19

1 Answers1

0

To simplify the notation, you can use the lpad function, like here:

with test as (
  select 1234567890123456789.1234 columnName from dual
  union select -7.12 from dual
  union select 108.0015 from dual
  )
select to_char(columnName, lpad('9', 19, '9')||'.9999') res
  from test

RES
------------------------
                 -7.1200
                108.0015
1234567890123456789.1234
Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24