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

TABLE OF CONTENTS:
  1. Oracle String Functions with PL/SQL
  2. Oracle instr() and replace() Function
  3. Oracle trim() and lpad() Function
  4. Oracle concat(), length(),
By: Codex-M
Rating: starstarstarstarstar / 3
May 05, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement


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
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: