[HACKERS] Oracle-compatible lpad/rpad behavior

Tom,

Hope this helps

From the Oracle manual:
Purpose
Returns char1, left-padded to length n with the sequence of characters
in char2; char2 defaults to a single blank. If char1 is longer than n,
this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is
displayed on your terminal screen. In most character sets, this is also
the number of characters in the return value. However, in some multibyte
character sets, the display length of a character string can differ from
the number of characters in the string.

and some examples (8.1.5 on NT):

SQL> select lpad('abcdef',3,'x') from dual;

LPA
---
abc

SQL> select lpad ('abcdef',8,'x') from dual;

LPAD('AB
--------
xxabcdef

SQL>
SQL> select lpad('abcdef',0,'x') from dual;

L
-

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)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