is it a bug?

... it seems the "lpad", "rpad" don't work,
when I type:
select lpad('laser', 4, 'a');
in psql, the result is still
'laser', the same with 'rpad',
Is it a bug or I'm mis-understaning the lpad and/or rpad functions?

A simple misunderstanding. The length argument is for the *total*
length. So padding a 5 character string to a length of 4 will do
nothing. But padding to a length of 6 will add a single "a" to the
string.

Seems the implementor made a mistake, since this is supposed to be oracle
compat stuff it should behave like Oracle, and thus trim the string to 4 chars.

Andreas

... it seems the "lpad", "rpad" don't work,
when I type:
select lpad('laser', 4, 'a');
in psql, the result is still
'laser', the same with 'rpad',
Is it a bug or I'm mis-understaning the lpad and/or rpad functions?

A simple misunderstanding. The length argument is for the *total*
length. So padding a 5 character string to a length of 4 will do
nothing. But padding to a length of 6 will add a single "a" to the
string.

- Thomas

"He weiping" <laser@zhengmai.com.cn> writes:

but it seems the "lpad", "rpad" don't work,
when I type:
select lpad('laser', 4, 'a');
in psql, the result is still=20
'laser', the same with 'rpad',
Is it a bug or I'm mis-understaning the lpad and/or rpad functions?

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?

regards, tom lane

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

Hi,

I think you've misunderstood the purpose of the functions.
They exist to *pad* the strings, not to truncate them.
Your examples will both return 'laser' because char_length('laser') = 5
and you asked for a padded length of 4.

Had you done this: select lpad('laser', 8, '*');
You would get this: ***laser

... and obviously with rpad() you would have seen 'laser***' instead.

If you want to truncate strings, try this:
select substring('laser' from 1 for 4);
... which will truncate to length 4, i.e. 'lase'

I couldn't find a combination function that will perform both of these
functions in one. However, you could try a construct like this:

select rpad(substring('laser' from 1 for xx), xx, '*');

... where 'xx' is the number of characters you want in the final string.
I'm sure you could wrap a user-defined function around this to that
you'd only have to feed in the number of characters once instead of
twice. Perhaps someone else knows a better way of doing this?

Hope this helps

Francis Solomon

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

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

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