The SQL LPAD and RPAD functions can be quite useful in your queries. Learn more about the LPAD and RPAD functions and see some examples in this article.
This article applies to Oracle, MySQL, and Postgres (SQL Server does not include LPAD or RPAD).
Purpose of the SQL LPAD and RPAD Functions
The SQL LPAD function takes a text value, and “pads” it on the left, by adding extra characters to the left of the value to meet a specified length.
It’s useful for ensuring that all values are the same length. It’s also used in hierarchical queries.
The purpose of the SQL RPAD function is to take a text value, and “pad” it on the right, by adding extra characters to the right of the value to meet a specified length.
It can be useful for ensuring that all values are the same length. Just like the LPAD function, it’s often used in hierarchical queries.
SQL LPAD and RPAD Syntax
The syntax of the LPAD function is:
LPAD( expr, length [, pad_expr] )The syntax of the RPAD function is:
RPAD( expr, length [, pad_expr] )Let’s have a look at what each of these parameters mean.
Parameters
The parameters of the SQL LPAD function are:
- expr (mandatory): This is the text value or text expression that you want to pad.
- length (mandatory): This is the total length value that the expr will be padded to.
- pad_expr (optional): This is the character or set of characters to use for padding the expr value. The default value is a single space.
The parameters of the SQL RPAD function are:
- expr (mandatory): This is the text value or text expression that you want to pad.
- length (mandatory): This is the total length value that the expr will be padded to.
- pad_expr (optional): This is the character or set of characters to use for padding the expr value. The default value is a single space.
Some things to note about the LPAD and RPAD functions:
- The length specifies the display length, and not the number of characters in the string. This is mostly the same, but for some multibyte character sets, it can be different. If you’re using VARCHAR values then it should match.
- If the length value is shorter than the length of expr, then the expr is trimmed to be length characters.
- If pad_expr is not specified, a single space is used.
- The return data type is TEXT.
Can You Use LPAD On a Number Column?
Yes, you can use LPAD with a number column. It works in the same way as using a text value.
See the examples section below for more information on how to do this.
Can You Use Oracle LPAD Without Truncating?
Yes, you can, but you need to use it along with other functions.
As mentioned above, if the length value is less than the length of the provided expression, then the expression is trimmed to be that length, rather than padded.
To avoid this will depend on your code and what you’re using the LPAD function for. You could use a LENGTH function to get the length of the string. You could use a large length value and then a SUBSTR to get the desired length.
The examples below show one way of doing it.
Why Is My LPAD Not Working?
There are a few reasons why your LPAD might not be working:
- The length might be less than the expression
- You might need to use RPAD instead of LPAD
- You might be trying to insert the wrong data type or size of data into a column
Once again, it depends on what you’re using the function for, so try to search for your exact problem and see if that helps.
Can You Use LPAD with NULL?
No, LPAD will not work with NULL values. If your expression contains a NULL value, or if you’re using a column that may contain a NULL value, you’ll need to use the NVL function first (or a function like COALESCE or DECODE to change it to a non-NULL value).
If you don’t use an NVL function or something similar, your return value will be NULL. This may or may not be the desired outcome.
So, instead of:
LPAD(columnname, 20, ' ')You’ll need to use something like:
LPAD(NVL(columnname, ' '), 20, ' ')See the examples below for more information
Can You Use Both SQL RPAD and LPAD Functions At Once?
Yes, you can use RPAD and LPAD in the same query. This is quite common, as sometimes you want to pad characters to both sides of a string.
See the examples section below for more information on how you can do this and what the results are.
Can You Use SQL RPAD with a NULL String?
No, RPAD will not work with NULL values. If the expression you’re providing contains a NULL value, or if you’re using a column that may contain a NULL value, you’ll need to use the NVL function first (or a function like COALESCE or DECODE to change it to a non-NULL value).
So, instead of this function:
RPAD(columnname, 15, ' ')You’ll need to use something like:
RPAD(NVL(columnname, ' '), 15, ' ')See the examples below for more information.
If you don’t use an NVL function or something similar, your return value will be NULL. This may or may not be the desired outcome.
Examples of the SQL LPAD Function
Here are some examples of the SQL LPAD function.
Example 1
This example uses LPAD with the default padding value.
SELECT
first_name,
LPAD(first_name, 10) AS padded_value
FROM customers;Result:
| FIRST_NAME | PADDED_VALUE |
| John | John |
| Sally | Sally |
| Steve | Steve |
| Adam | Adam |
| Josh | Josh |
| Peter | Peter |
| Mark | Mark |
The names have all been padded with spaces on the left to be the same length.
Example 2
This example uses a * as the padding value.
SELECT
first_name,
LPAD(first_name, 10, '*') AS padded_value
FROM customers;Result:
| FIRST_NAME | PADDED_VALUE |
| John | ******John |
| Sally | *****Sally |
| Steve | *****Steve |
| Adam | ******Adam |
| Josh | ******Josh |
| Peter | *****Peter |
| Mark | ******Mark |
The values have had a * character padded to the left instead of a space.
Example 3
This example uses a length that is less than some of the values.
SELECT
first_name,
LPAD(first_name, 4, ' ') AS padded_value
FROM customers;Result:
| FIRST_NAME | PADDED_VALUE |
| John | John |
| Sally | Sall |
| Steve | Stev |
| Adam | Adam |
| Josh | Josh |
| Peter | Pete |
| Mark | Mark |
Some of the values are trimmed to match the length value of 4.
Example 4
This example uses LPAD on a number column.
SELECT
employees,
LPAD(employees, 5, ' ') AS padded_value
FROM customers;Result:
| EMPLOYEES | PADDED_VALUE |
| 4 | 4 |
| 10 | 10 |
| 15 | 15 |
| 55 | 55 |
| 1 | 1 |
| (null) | (null) |
| 23 | 23 |
The values are padded in the same way as a string value.
Example 5
This example uses LPAD and RPAD in one statement, with different characters to show you how they are added.
SELECT
last_name,
RPAD(LPAD(last_name, 10, '#'), 15, '*') AS padded_value
FROM customers;Result:
| LAST_NAME | PADDED_VALUE |
| Smith | #####Smith***** |
| Jones | #####Jones***** |
| Brown | #####Brown***** |
| Cooper | ####Cooper***** |
| Thompson | ##Thompson***** |
| Manson | ####Manson***** |
| Allan | #####Allan***** |
The value is padded with # on the left, which varies to match the 10 character length, and then a set of * to bring the 10 characters up to 15 characters.
Example 6
This example uses the NVL function with a NULL value.
SELECT
country,
LPAD(NVL(country, ' '), 10, '_') AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | _______USA |
| USA | _______USA |
| Canada | ____Canada |
| USA | _______USA |
| _________ | |
| France | ____France |
| UK | ________UK |
The values which are not null are padded, and the null value is represented as a space character.
Example 7
This example uses a NULL value as the padding expression, just to see what happens.
SELECT
country,
LPAD(country, 10, NULL) AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | (null) |
| USA | (null) |
| Canada | (null) |
| USA | (null) |
| (null) | (null) |
| France | (null) |
| UK | (null) |
All of the values are changed to NULL, which is probably not what you wanted.
Example 8
This example shows how to use LPAD with multiple characters as your pad_expr.
SELECT
country,
LPAD(country, 10, '-*') AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | -*-*-*-USA |
| USA | -*-*-*-USA |
| Canada | -*-*Canada |
| USA | -*-*-*-USA |
| (null) | (null) |
| France | -*-*France |
| UK | -*-*-*-*UK |
The country values are padded with a combination of – and *. If there is not an exact match between padding characters and places remaining, then only part of the padding expression is used.
Example 9
This example uses a dynamic length, which is based on the length of each value.
SELECT
country,
LPAD(country, LENGTH(country)*2, '*') AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | ***USA |
| USA | ***USA |
| Canada | ******Canada |
| USA | ***USA |
| (null) | (null) |
| France | ******France |
| UK | **UK |
The values are padded to different lengths, as determined by the LENGTH function.
Example 10
This example uses RPAD with the default padding value.
SELECT
first_name,
RPAD(last_name, 15) AS padded_value
FROM customers;Result:
| FIRST_NAME | PADDED_VALUE |
| John | Smith |
| Sally | Jones |
| Steve | Brown |
| Mark | Allan |
| Adam | Cooper |
| Josh | Thompson |
| Peter | Manson |
| (null) | (null) |
The names have all been padded with spaces on the right to be the same length.
Example 11
This example uses a * as the padding value.
SELECT
first_name,
RPAD(last_name, 15, '*') AS padded_value
FROM customers;Result:
| FIRST_NAME | PADDED_VALUE |
| John | Smith********** |
| Sally | Jones********** |
| Steve | Brown********** |
| Mark | Allan********** |
| Adam | Cooper********* |
| Josh | Thompson******* |
| Peter | Manson********* |
| (null) | (null) |
The values have had a * character padded to the right instead of a space.
Example 12
This example uses a length that is less than some of the values.
SELECT
first_name,
RPAD(last_name, 3, ' ') AS padded_value
FROM customers;Result:
| FIRST_NAME | PADDED_VALUE |
| John | Smith |
| Sally | Jones |
| Steve | Brown |
| Mark | Allan |
| Adam | Coope |
| Josh | Thomp |
| Peter | Manso |
| (null) | (null) |
Some of the values are trimmed to match the length value of 5.
Example 13
This example uses RPAD on a number column.
SELECT
employees,
RPAD(employees, 4, ' ') AS padded_value
FROM customers;Result:
| EMPLOYEES | PADDED_VALUE |
| 4 | 4 |
| 10 | 10 |
| 15 | 15 |
| 23 | 23 |
| 55 | 55 |
| 1 | 1 |
| (null) | (null) |
| (null) | (null) |
The values are padded in the same way as a string value.
Example 14
This example uses LPAD and RPAD in the one statement, with different characters to show you how they are added.
SELECT
last_name,
RPAD(LPAD(last_name, 10, '#'), 15, '*') AS padded_value
FROM customers;Result:
| LAST_NAME | PADDED_VALUE |
| Smith | #####Smith***** |
| Jones | #####Jones***** |
| Brown | #####Brown***** |
| Allan | #####Allan***** |
| Cooper | ####Cooper***** |
| Thompson | ##Thompson***** |
| Manson | ####Manson***** |
| (null) | (null) |
The value is padded with # on the left, which varies to match the 10 character length, and then a set of * to bring the 10 characters up to 15 characters.
Example 15
This example uses the NVL function with a NULL value.
SELECT
country,
RPAD(NVL(country, ' '), 10, '_') AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | USA_______ |
| USA | USA_______ |
| Canada | Canada____ |
| UK | UK________ |
| USA | USA_______ |
| (null) | _________ |
| France | France____ |
| (null) | _________ |
The values which are not null are padded, and the null value is represented as a space character.
Example 16
This example uses a NULL value as the padding expression, just to see what happens.
SELECT
country,
RPAD(country, 8, NULL) AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | (null) |
| USA | (null) |
| Canada | (null) |
| UK | (null) |
| USA | (null) |
| (null) | (null) |
| France | (null) |
| (null) | (null) |
All of the values are changed to NULL, which is probably not what you wanted.
Example 17
This example shows how to use LPAD with multiple characters as your pad_expr.
SELECT
country,
RPAD(country, 15, '-*') AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | USA-*-*-*-*-*-* |
| USA | USA-*-*-*-*-*-* |
| Canada | Canada-*-*-*-*- |
| UK | UK-*-*-*-*-*-*- |
| USA | USA-*-*-*-*-*-* |
| (null) | (null) |
| France | France-*-*-*-*- |
| (null) | (null) |
The country values are padded with a combination of – and *. If there is not an exact match between padding characters and places remaining (such as with Canada or UK), then only part of the padding expression is used.
Example 18
This example uses a dynamic length, which is based on the length of each value.
SELECT
country,
RPAD(country, LENGTH(country)*2, '*') AS padded_value
FROM customers;Result:
| COUNTRY | PADDED_VALUE |
| USA | USA*** |
| USA | USA*** |
| Canada | Canada****** |
| UK | UK** |
| USA | USA*** |
| (null) | (null) |
| France | France****** |
| (null) | (null) |
The values are padded to different lengths, as determined by the LENGTH function. Each name has a different number of * characters after it.
If you’re interested in other Oracle functions, you can find a full list of Oracle functions here.