The Oracle 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.

Purpose of the Oracle LPAD and RPAD Functions

The Oracle 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.Oracle LPAD RPAD

It’s useful for ensuring that all values are the same length. It’s also used in hierarchical queries.

The purpose of the Oracle 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.

 

Oracle 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.

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Parameters

The parameters of the Oracle 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 Oracle 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 VARCHAR2 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 a 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 Oracle 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 Oracle 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 a NVL function or something similar, your return value will be NULL. This may or may not be the desired outcome.

 

Examples of the Oracle LPAD Function

Here are some examples of the Oracle LPAD function. I find that examples are the best way for me to learn about code, even with the explanation above.

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 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*****
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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Image courtesy of digitalart / FreeDigitalPhotos.net

Improve Your Oracle SQL With My 10-Day Email Course

x