HomeOracle Page 3 - Oracle String Functions with PL/SQL
Oracle trim() and lpad() Function - Oracle
This is an introductory tutorial of the most commonly used Oracle string functions using PL/SQL. Oracle string functions help you become efficient in retrieving string information from an Oracle database.
trim() will remove specified characters either from the start or the end of the string. Syntax:
trim([leading|trailing|both trim_character FROM string)
If the trim character is omitted there will be no options for leading and trailing parameters - it will simply remove the trailing and leading spaces. It will then behave like the MS Excel trim function.
If the trim character is omitted there will be no options for leading and trailing parameters - it will simply remove the trailing and leading spaces. It will then behave like the MS Excel trim function.Example:
trim(leading 'H' from 'Hello World') returns 'ello World' trim(trailing 'd' from 'Hello World') returns 'Hello Worl' trim(both 'X' from 'XCodexX') returns 'Codex'
Note: This cannot be used to remove more than one character, for example as in this SQL query:
SELECT trim(leading 'Dr.' from "FirstName") FIRSTNAME,"LastName" LASTNAME from "DoctorsList";
This will return an error:
ORA-30001: trim set should have only one character
Oracle lpad() Function
This function is useful when adding/padding the left side of the string with specific characters. For example, if you have the string, "hello world" and would like to add this set of characters - "xyz", making it "xyzhello world" - you need to use the lpad function. Syntax:
If the paddinglength is less than the length of the given string, it will truncate the string.
lpad('hello world',2, 'xyz')
will not pad anything, since padding length is less than the length of the "hello world" string. Instead, it will truncate and return only “he”
lpad('hello world',14, 'xyz')
since the length of "hello world" is 11 characters and xyz is 3 characters. So the entire padded string is added (by defining 14 as the padding length).