
> lpad and rpad never truncate, they only pad.
> 
> Perhaps they *should* truncate if the specified length is less than
> the original string length.  Does Oracle do that?

Yes, it truncates, same as Informix.

Andreas

Tue, 28 Nov 2000 16:30:49 +0100
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: [HACKERS] Re: is it a bug? 
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> lpad and rpad never truncate, they only pad.
>> 
>> Perhaps they *should* truncate if the specified length is less than
>> the original string length.  Does Oracle do that?

> Yes, it truncates, same as Informix.

I went to fix this and then realized I still don't have an adequate spec
of how Oracle defines these functions.  It would seem logical, for
example, that lpad might truncate on the left instead of the right,
ie lpad('abcd', 3, 'whatever') might yield 'bcd' not 'abc'.  Would
someone check?

Also, what happens if the specified length is less than zero?  Error,
or is it treated as zero?

			regards, tom lane

Thu, 07 Dec 2000 14:51:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Oracle-compatible lpad/rpad behavior
> I went to fix this and then realized I still don't have an adequate spec
> of how Oracle defines these functions.  It would seem logical, for
> example, that lpad might truncate on the left instead of the right,
> ie lpad('abcd', 3, 'whatever') might yield 'bcd' not 'abc'.  Would
> someone check?

SQL> select lpad('abcd', 3, 'foobar') from dual;

LPA
---
abc

> Also, what happens if the specified length is less than zero?  Error,
> or is it treated as zero?

SQL> select ':' || lpad('abcd', -1, 'foobar') || ':' from dual;

':
--
::

(colons added so it's obvious that it's a zero-length string)

-Jonathan


Thu, 7 Dec 2000 14:30:24 -0700
"Jonathan Ellis" <jellis@advocast.com>
Re: Oracle-compatible lpad/rpad behavior
Jonathan Ellis wrote:
: > Also, what happens if the specified length is less than zero?  Error,
: > or is it treated as zero?
: 
: SQL> select ':' || lpad('abcd', -1, 'foobar') || ':' from dual;
: 
: ':
: --
: ::
: 
: (colons added so it's obvious that it's a zero-length string)

Returns not empty string but NULL:

SQL> select nvl(lpad('abcd', -1, 'foobar'), 'Null') from dual;

NVL(
----
Null

-- 
Andrew W. Nosenko    (awn@bcs.zp.ua)

Fri, 8 Dec 2000 09:33:46 +0200
Andrew Nosenko <awn@bcs.zp.ua>
Re: Oracle-compatible lpad/rpad behavior
> Returns not empty string but NULL:

The two are equivalent in Oracle.  Try select 'a' || null || 'b' from dual
and compare it to postgres.

-Jonathan


Fri, 8 Dec 2000 08:46:19 -0700
"Jonathan Ellis" <jellis@advocast.com>
Re: Oracle-compatible lpad/rpad behavior
Jonathan Ellis wrote:
> 
> > I went to fix this and then realized I still don't have an adequate spec
> > of how Oracle defines these functions.  It would seem logical, for
> > example, that lpad might truncate on the left instead of the right,
> > ie lpad('abcd', 3, 'whatever') might yield 'bcd' not 'abc'.  Would
> > someone check?
> 
> SQL> select lpad('abcd', 3, 'foobar') from dual;
> 
> LPA
> ---
> abc
> 
> > Also, what happens if the specified length is less than zero?  Error,
> > or is it treated as zero?
> 
> SQL> select ':' || lpad('abcd', -1, 'foobar') || ':' from dual;
> 
> ':
> --
> ::
> 
> (colons added so it's obvious that it's a zero-length string)

AFAIK Oracle is unable to distinguish NULL and zero-length string ;(

--------------
Hannu

Tue, 12 Dec 2000 19:00:39 +0000
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] Re: Oracle-compatible lpad/rpad behavior