如何将oracle数字类型转换为具有格式的字符串

Question

我想将数字类型转换为字符串，格式为：

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

Answer 1

您可以使用TO_CHAR（）函数（在这种情况下更可取）或LPAD（）函数来实现所需的结果：

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