Re: Oralce BUG or feature???

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1998/09/26
Message-ID: <36162176.8201687@netnews.worldnet.att.net>#1/1

On 25 Sep 1998 14:30:40 PDT, Nnoor_at_cris.com (NNOOR) wrote:

>select LPAD(class_code, 4, '0') from tableName
>returns only the original value. For example 612 comes back
>as 612 and not 0612.

Is it possible that your text field already has leading spaces? If so, LPAD won't do what you are asking. Note the following two calls to LPAD. The leading spaces in the second call aren't touched.

SQL>
SQL> select lpad ('602',4,'0') from dual;

LPAD

---

0602

SQL> select lpad(' 602',4,'0') from dual;

LPAD

---

 602

If this is the case with your data, use LTRIM to eliminate any leading spaces, and then pad the result of that, for example:

SQL> select lpad(ltrim(' 602',' '),4,'0') from dual;

LPAD

---

0602

regards,

Jonathan Received on Sat Sep 26 1998 - 00:00:00 CDT