Re: Oralce BUG or feature???

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/26
Message-ID: <360c34a0.555578@192.86.155.100>#1/1

A copy of this was sent to Nnoor_at_cris.com (NNOOR) (if that email address didn't require changing) On 25 Sep 1998 14:30:40 PDT, you wrote:

>
>I am using Oracle 7.3.3 on NT 4.
>
>I have a table with a field of type VARCHAR(5). It has numerical
>values in it. I was trying to pad zeros at the front of it (e.g.
>612 would become 0612), that's when I discovered this behaviour
>of LPAD function:
>
>select LPAD(class_code, 4, '0') from tableName
>returns only the original value. For example 612 comes back
>as 612 and not 0612.
>
>select LPAD(class_code, 4, 'A') from tableName
>returns nothing!
>
>RPAD seems to work OK. I guess that internally Oracle is treating
>the numerical values as true number and not char.
>

no, lpad works like this:

SQL> create table t1 ( x varchar2(5) );
Table created.

SQL> insert into t1 values ( '612' );
1 row created.

SQL> select lpad( x, 4, '0' ) from t1;

LPAD

0612

SQL> select lpad( x, 4, 'A' ) from t1;

LPAD

A612

can you post an example that does differently? also, include versions/platforms etc...

>How do I pad leading zero's in a varchar field?
>
>Thanks.
>Nasir (nnoor_at_cris.com)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

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