Introduction to the PostgreSQL RPAD() function

TheRPAD() function allows you to extend a string to a length by appending specified characters.

Here’s the basic syntax of theRPAD() function:

RPAD(string, length, fill)

In this syntax:

• string: The input string that you want to extend.
• length: The desired length of the string after padding.
• fill: The character or string used for padding.

TheRPAD() function returns the string, right-padded with the stringfill to a length oflength characters.

If the length of thestring is greater than the desiredlength, theRPAD() function truncates thestring to thelength characters.

If any argumentstring,length, orfill isNULL, theRPAD() function returnsNULL.

TheRPAD() function can be particularly useful when you need to format text with a consistent length, align text in columns, or prepare data for display.

To left-pad a string to a length with specified characters, you can use theLPAD() function.

PostgreSQL RPAD() function examples

Let’s explore some examples of using the PostgreSQLRPAD() function.

1) Basic PostgreSQL RPAD() function

The following example uses theRPAD() function to extend a string by filling zeros (‘0’) to make it six characters long:

SELECT RPAD('123',6,'0');

Output:

rpad-------- 123000(1 row)

2) Using the RPAD() function with the table data example

We’ll use thefilm table from thesample database:

The following example uses theRPAD() function to right-pad the titles from thefilm table with the character ‘.’ to make it 50 characters long:

SELECT  RPAD(title,50,'.')FROM  film;

Output:

rpad---------------------------------------------------- Chamber Italian................................... Grosse Wonderful.................................. Airport Pollock................................... Bright Encounters................................. Academy Dinosaur.....................................

3) Using the RPAD() function to truncate strings

The following example uses theRPAD() function to truncate the titles if their lengths are more than 10 characters:

SELECT  title, RPAD(title, 10, '')resultFROM  film;

Output:

title            |   result-----------------------------+------------ Chamber Italian             | Chamber It Grosse Wonderful            | Grosse Won Airport Pollock             | Airport Po Bright Encounters           | Bright Enc Academy Dinosaur            | Academy Di Ace Goldfinger              | Ace Goldfi...

Summary

• Use PostgreSQLRPAD() function to extend a string to a length by appending specified characters.

Last updated onJanuary 29, 2024

On this page

• Introduction to the PostgreSQL RPAD() function
• PostgreSQL RPAD() function examples
• Summary