Padding a string in Postgresql with rpad without truncating it

Posted by dmoebius on Stack Overflow See other posts from Stack Overflow or by dmoebius
Published on 2012-08-27T18:40:04Z Indexed on 2012/10/10 9:38 UTC
Read the original article Hit count: 234

Using Postgresql 8.4, how can I right-pad a string with blanks without truncating it when it's too long?

The problem is that rpad truncates the string when it is actually longer than number of characters to pad. Example:

SELECT rpad('foo', 5);  ==> 'foo  ' -- fine
SELECT rpad('foo', 2);  ==> 'fo'    -- not good, I want 'foo' instead.

The shortest solution I found doesn't involve rpad at all:

SELECT 'foo' || repeat(' ', 5-length('foo'));  ==> 'foo  ' -- fine
SELECT 'foo' || repeat(' ', 2-length('foo'));  ==> 'foo'   -- fine, too

but this looks ugly IMHO. Note that I don't actually select the string 'foo' of course, instead I select from a column:

SELECT colname || repeat(' ', 30-length(colname)) FROM mytable WHERE ...

Is there a more elegant solution?

© Stack Overflow or respective owner

Related posts about sql

Related posts about string