Bug #14881 Inconsistency of behaviour between lpad() and rpad()?
Submitted: 11 Nov 2005 19:24 Modified: 12 Nov 2005 15:53
Reporter: Gisbert Selke (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.15 OS:Windows (Win XP)
Assigned to: MySQL Verification Team CPU Architecture:Any

[11 Nov 2005 19:24] Gisbert Selke 
Description:
I found that RPAD() works symmetrical to LPAD() when actually padding -- which is what I would expect --, but non-symmetrical when shortening -- which I find surprising.
(Admittedly, this issue doesn't seem to be covered by the standards, hence, one is free to define whatever. Still...)
 

How to repeat:
SELECT lpad("123", 6, "*"),rpad("123", 6, "*"),lpad("123", 1, "*"),rpad("123", 1, "*")
I expect:
'***123', '123***', '3', '1'
but get:
'***123', '123***', '1', '1'
[11 Nov 2005 23:21] MySQL Verification Team 
How you can see our documentation the server behaves as documented:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr to a length of len characters.
If str is longer than len, the return value is shortened to len characters.

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'

RPAD(str,len,padstr)

Returns the string str, right-padded with the string padstr to a length of len characters.
If str is longer than len, the return value is shortened to len characters.

mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
        -> 'h'

I understood what you meant, then I did a search how these functions
are handled by others products and verified that behaves the same way
as MySQL.
[12 Nov 2005 15:53] Gisbert Selke 
I agree, it is not a bug. I would suggest to make the docs for both rpad() and lpad() clearer, hoewever. Instead of 
"If str is longer than len, the return value is shortened to len characters."
I would suggest
"If str is longer than len, the return value is shortened from the right to len characters."
That would spare the user some experimenting (and some surprise, maybe).