Continuing on the blog post series of Single-Row Character Functions, we are going to discuss and learn about the following 5 functions: LPAD, RPAD, LTRIM, RTRIM, TRIM. Sorry guys, there is no PAD function :(

These functions are great at formatting the output that is returned by your query. Of course you could use them not only in the SELECT clause, but also in the WHERE clause of a SQL Statement, a more uncommon approach.

LPAD (which means left pad), returns the the input character, left-padded (to the left side) with the specified characters, up to a specific length.
RPAD (which means right pad), returns the the input character, right-padded (to the right side) with the specified characters, up to a specific length.

For both of these functions you need to specify the input character string/column, the lenght of the padded string, and the padding characters. Let’s have a look:

LPAD(character1, n, character2)
LPAD(last_name , 20, '*')
LPAD('Robete' , 10, '*')

The first LPAD example shows that the last_name column will be left padded with the * character, up to a length of 20 characters. The second example shows that the character string ‘Robete’ (notice the single quotes around), will be left padded with ‘*’ character, up to 10 characters.

RPAD(character1, n, character2)
RPAD(last_name , 20, '*')
RPAD('Robete'  , 10, '*')

The first RPAD example shows that the last_name column will be right padded with the * character, up to a length of 20 characters. The second example shows that the character string ‘Robete’ (notice the single quotes around), will be right padded with ‘*’ character, up to 10 characters.

For both functions, LPAD and RPAD, both, character1 and character2 can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB data type. The return type of these functions is character data type. n must be an integer, or a value that can be implicitly converted to a number. What happens if n is negative? character1 is mandatory as an input, meaning you will get an error if you don’t specify it. character2 is optional, meaning you can omit it. In that case the default character is a single blank. So LPAD(‘Robete’ , 10, ‘ ‘) would be equivalent to LPAD(‘Robete’ , 10), in both cases the character string ‘Robete’ is left padded with a blank character, to a total length of 10 characters. Also character2 can have more than one characters, however, usually we see one character used, but you could use multiple ones: LPAD(‘Robete’ , 10, ‘*+’)

If character1 is longer than the length n you specify, then the function will return the part of the character1, up to the length n. In this case LPAD(‘Robete’,4,’*’), will return ‘Robe’ only.

As with other character functions, you can have the character1 as a column name, character string, literal, substitution variable or bind variable, you pick!

Let’s look at some examples!

select Lpad('Robete', 10 ,'*') left_padded
     ,Rpad('Robete', 10 ,'*') right_padded
from dual;

LEFT_PADDED RIGHT_PADDED
----------- ------------
****Robete  Robete****

select lpad(last_name,15,'*_') left_name
     ,rpad(last_name,15,'*_') right_name
from hr.employees;
 

LEFT_NAME       RIGHT_NAME
--------------- ---------------
*_*_*_*_*Robete Robete*_*_*_*_*
...

select lpad('&&_lname', 15, ' ')
     ,rpad('&_lname' , 15, ' ')
from dual;

old   1: select lpad('&&_lname', 15, ' '), rpad('&_lname',15,' ' ) from dual
new   1: select lpad('Diana', 15, ' '), rpad('Diana',15,' ' ) from dual

LPAD('DIANA',15 RPAD('DIANA',15
--------------- ---------------
         Diana Diana

select lpad('Robete',-10,'*') from dual;
L
-

--nothing is returned when n is negative

Let’s display the salary of employees visually, as one ‘*’ for each thousands of dollars they make.

select last_name, rpad(' ', trunc(salary/1000)+1,'*') as salary
from hr.employees
order by last_name, salary;

LAST_NAME                 SALARY
------------------------- ---------------
Abel                       *********
Ande                       ******
Banda                      *****
Bates                      *******
Bernstein                  ********
...

Let’s look at LTRIM and RTRIM functions, which are also similar functions!

LTRIM will left trim a specified character string, it will remove starting at the left end, all of the characters you specify in a set (one or more characters). The specified character string will be scanned starting at the left side, and all the characters that are specified in the set, will be removed, until Oracle comes across a character that is not in the set.

RTRIM will right trim a specified character string, it will remove starting at the right end, all of the characters you specify in a set (one or more characters). The specified character string will be scanned starting at the right side, and all the characters that are specified in the set, will be removed, until Oracle comes across a character that is not in the set.

LTRIM(character_string, character_set)
LTRIM(‘+++Robete’,’+*’)
LTRIM(‘ Robete’)

RTRIM(character_string, character_set)
RTRIM(‘+++Robete++*‘,’+‘)
RTRIM(‘Robete ‘)

character_set is optional in this case. If you don’t specify it, then it will default to a single blank character.

select ltrim('***____***Diana**_**','*_') ltrim_string
     ,rtrim('***____***Diana**_**','*_') rtrim_string
from dual;

LTRIM_STRI RTRIM_STRING
---------- ---------------
Diana**_** ***____***Diana

If we want to trim a specified character sets from both ends of a character string, then we can use the TRIM function!

The TRIM functions’ syntax is a bit different from LTRIM and RTRIM.
For TRIM you can specify LEADING | TRAILING | BOTH | or nothing; LEADING would be for the left side, and TRAILING for the right side.

TRIM(LEADING '*' from '*+++*Robete++**')
TRIM(TRAILING '*' from '*+++*Robete++**')
TRIM(BOTH '*' from '*+++*Robete++**')
TRIM('*' from '*+++*Robete++**')
TRIM('  +++*Robete++**')

If the trim character is not specified, as in the last example, then it defaults to a blank space. If you only specify the source, then blank spaces will be removed from left and right of the source character string

select TRIM(LEADING '*' from '*+++*Robete++**')  lead_char
     ,TRIM(TRAILING '*' from '*+++*Robete++**') trail_char
     ,TRIM(BOTH '*' from '*+++*Robete++**')     both_char1
     ,TRIM('*' from '*+++*Robete++**')          both_char2
     ,TRIM('  +++*Robete++**')                  blank_char
from dual;

LEAD_CHAR      TRAIL_CHAR    BOTH_CHAR1   BOTH_CHAR2   BLANK_CHAR
-------------- ------------- ------------ ------------ -----------------
+++*Robete++** *+++*Robete++ +++*Robete++ +++*Robete++ +++*Robete++**

By the end of this email you know how to TRIM and PAD! Do you use these functions often?