Thread: AW: Oracle-compatible lpad/rpad behavior

> >> 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?

returns 'abc' on Oracle and Informix.

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

Returns NULL in both if length <= 0. I would see the < 0 case as proper,
but the == 0 case sure looks weird to me.

Very good catch, Tom !! :-)
Andreas

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> Also, what happens if the specified length is less than zero?  Error,
>> or is it treated as zero?

> Returns NULL in both if length <= 0. I would see the < 0 case as proper,
> but the == 0 case sure looks weird to me.

Since Oracle fails to distinguish NULL from empty string, it's hard to
tell what they have in mind here.  I've implemented it as empty-string
result for length <= 0.  You could possibly make a case for empty string
at length = 0 and NULL for length < 0, but I'm not sure it's worth the
trouble...

            regards, tom lane