Results 1 to 2 of 2

Thread: padding number

  1. #1
    Join Date
    Nov 2004

    Unanswered: padding number

    i have a vaiable declared using number
    how do i left pad it with 0?
    i tried lpad(num,4,'0') but it does not seems to work..
    any reason?

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    I'd say you can't do that, as long as the variable is declared as a number. As you probably know, '0000000001' equals 1; numbers in Oracle are represented in such a way as well.

    If you need to show leading zeros, change variable's datatype to a character.
      2    myvar NUMBER(10) := 1;
      3    my_numpad_var NUMBER(10);
      4    my_charpad_var VARCHAR2(10);
      5  BEGIN
      6    SELECT LPAD(myvar, 4, 0), LPAD(myvar, 4, 0) 
      7      INTO my_numpad_var, my_charpad_var
      8     FROM dual;
      9    dbms_output.put_line('Num ' đđ my_numpad_var đđ'; Char ' đđ my_charpad_Var);
     10  END;
     11  /
    Num 1; Char 0001
    PL/SQL procedure successfully completed.
    If you try that with a table column that is a number, you'll get "correct" result. For example
    SQL> SELECT LPAD(deptno, 4, 0) FROM dept;
    but that's a "fake" as during SELECT operations, Oracle converts data from the column to the type of the target variable and shows the result which may confuse you.

    Oracle recommends that you specify explicit conversions rather than rely on implicit conversions as your statements are easier to understand, you won't have a negative impact on performance and you might get unexpected result (for example, when you convert datetime to a varchar2, result will depend on NLS_DATE_FORMAT parameter).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts