Oracle trim() and lpad() Function - Oracle

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.

Example:

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:

lpad(givenstring,paddinglength, paddedstring)

If the paddinglength is less than the length of the given string, it will truncate the string.

Examples:

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”

But:

lpad('hello world',14, 'xyz')

will return:

xyzhello world

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

>>> More Oracle Articles          >>> More By Codex-M

blog comments powered by Disqus