Geek Answers Handbook

Postgresql string filling with rpad without truncation

Using Postgresql 8.4, how can I draw a line with spaces correctly without truncating it when it is too long?

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

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

The shortest solution found does not include rpad :

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

but it looks ugly IMHO. Please note that I really do not select the string "foo", of course, instead I select from the column:

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

Is there a more elegant solution?

+4
source share
4 answers

found a slightly more elegant solution:

 SELECT greatest(colname,rpad(colname, 2));

eg:

 SELECT greatest('foo',rpad('foo', 5)); -- 'foo ' SELECT greatest('foo',rpad('foo', 2)); -- 'foo'

.

To explain how this works: rpad ('foo', 5) = 'foo', which has the value '' foo '' (the largest number works with both strings and numbers) rpad ('foo', 2) = 'fo', which is equal to <'foo', so 'foo' is selected by the largest function.

if you need left words that you cannot use anymore because they are compared from left to right (for example, "oo" with "foo"), and in some cases it will be more or less depending on the string. I assume that you can change the line and use rpad and cancel it back, or just use the original solution, which works in both cases.

+6
source

If you do not want to constantly write this repeat business, just write your own function for it. Something like that:

 create or replace function rpad_upto(text, int) returns text as $$ begin if length($1) >= $2 then return $1; end if; return rpad($1, $2); end; $$ language plpgsql;

or that:

 create or replace function rpad_upto(text, int) returns text as $$ select $1 || repeat(' ', $2 - length($1)); $$ language sql;

Then you can say things like:

 select rpad_upto(colname, 30) from mytable ...

You might want to consider what you want rpad_upto(null, n) do while you are on it. Both versions of rpad_upto above will return NULL if $1 is NULL, but you can configure them to return something else without much difficulty.

+7
source

how about this

 select case when length(col) < x then rpad(col, x) else col end from table
+3
source

Estimated efficiency here is not the biggest problem:

 select regexp_replace(format('%5s', 'foo'), '(\s*)(\S*)', '\2\1')
  • format () aligns the string to the required width
  • then regexp_replace moves all leading spaces to the end.

I assume this will fail if you have leading spaces in the lines and you want to keep them. Also note that format () does not return null for null parameters.

0
source

More articles:


All Articles