Thread: AW: [HACKERS] Re: is it a bug?
> 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
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
> 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
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)
> 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
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