CODEWITHSUNDEEP
stringoracle-databasenumbersformatconverters
Hau Le
Hau Le

Reputation: 677

How to convert oracle number type to string with format?

I want to convert number type to string with format as:

number -> string

    1      -> 001
    2      -> 002
    12     -> 012
    340    -> 340

Upvotes: 1

Views: 6643

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

You can use either TO_CHAR() (preferable in this situation) function or LPAD() function to achieve the desired result:

SQL> with t1(col) as(
  2    select 1   from dual union all
  3    select 2   from dual union all
  4    select 12  from dual union all
  5    select 340 from dual
  6  )
  7  select to_char(col, '000')        as num_1
  8       , lpad(to_char(col), 3, '0') as num_2
  9    from t1
 10  ;

NUM_1 NUM_2
----- ------------
 001  001
 002  002
 012  012
 340  340

Upvotes: 7

Related Questions