The PostgreSQL LPAD() function pads a string on the left to a specified length with a sequence of characters.
Syntax
The following illustrates the syntax of the LPAD() function:
1 | LPAD(string, length[, fill]) |
Arguments
The LPAD() function accepts 3 arguments:
1) string
is a string that should be padded on the left
2) length
is an positive integer that specifies the length of the result string after padding.
Noted that if the string is longer than the length argument, the string will be truncated on the right.
3) fill
is a string used for padding.
The fill argument is optional. If you omit the fill argument, its default value is a space.
Return value
The PostgreSQL LPAD() function returns a string left-padded to length characters.
Examples
Let’s see some examples of using the LPAD() function.
The following statement uses the LPAD() function to pad the ‘*’ on the left of the string ‘PostgreSQL’:
1 | SELECT LPAD('PostgreSQL',15,'*'); |
The result is:
1 2 3 4 | lpad ---------------- *****PostgreSQL (1 row) |
In this example, the length of the PostgreSQL string is 10, the result string should have the length 15, therefore, the LPAD() function pads 5 character * on the left of the string.
See the following customer and payment tables from the sample database:
The following statement illustrates how to use the LPAD() function to draw a chart based on the sum of payments per customer.
1 2 3 4 5 6 7 | SELECT first_name || ' ' || last_name fullname, SUM(amount) total, LPAD('*', CAST(TRUNC(SUM(amount) / 10) AS INT), '*') chart FROM payment INNER JOIN customer using (customer_id) GROUP BY customer_id ORDER BY SUM(amount) DESC; |
The following picture illustrates the result:
In this example,
- First, we added up the payments per each customer using the
SUM()function and theGROUP BYclause, - Second, we calculated the length of the bar chart based on the sums of payments using various functions:
TRUNC()to truncate the total payments,CAST()to convert the result ofTRUNC()to an integer. To make the bar chart more readable, we divided the sum of payments by 10. - Third, we applied the
LPAD()function to pad the character (*) based on the result of the second step above.
In this tutorial, you have learned how to use the PostgreSQL LPAD() function to pad characters on the left of a string to a certain length.