Do You Know How To TRIM or PAD Character Strings?
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 post you know how to TRIM and PAD! Do you use these functions often?
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !
