Oracle String Functions with PL/SQL

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.

This example in this tutorial have been tested on:

a.) Ubuntu 10.04 Lucid Lynx OS
b.) Oracle Database 10g Express Edition

The following is a list of string functions we will be discussing in this tutorial:

1.) substr
2.) instr
3.) replace
4.) trim
5.) lpad
6.) concat
7.) translate
8.) length

For the best learning experience, I recommended that you have Oracle Database 10g Express edition installed on your system so that you can test the examples illustrated in this tutorial.

Oracle substr() Function

One of the most commonly used Oracle string functions is substr. This function is used to extract specific characters from a string. It works similarly to substr in MS Excel. Supposing the given string is: Hello World and you want to extract the word “World”, one method you could use would be the substr function. This is the syntax of this function:

substr(string, starting_position, length)

“Starting position” is always 1 if it starts on the 1st character of the string. “Length” is the number of characters you want to extract from the beginning of “starting_position”. Illustrating the previous example of a given string “Hello World” and you want to extract the word “World”, the following is the substr syntax you would use:

substr(“Hello World”, 1, 5)

Since starting from the “H” to “O”(the end of word “Hello”) there are 5 characters to extract, so the length is 5. So how do you use this string function in your Oracle SQL queries? Follow the steps below:

1.) In Ubuntu, go to Applications – Oracle Database 10g Express edition – click “Go to Database Homepage”

2.) Login as database administrator.

3.) Click “SQL”.

4.) Click “SQL” Commands.

5.) In the input text box area, you need to create a sample Oracle table. Copy and paste the query below:

CREATE TABLE “DoctorsList”
( “ID” NUMBER (6,0),
“FirstName” VARCHAR2(35),
“LastName” VARCHAR2(35),

6.) Click “Run”.7.) You need to insert sample data; copy and paste the query below then click “Run”:

INTO “DoctorsList” VALUES
( 1
, ‘Dr. Meri’
, ‘Keller’
INTO “DoctorsList” VALUES
( 2
, ‘Dr. John’
, ‘Know’
INTO “DoctorsList” VALUES
( 3
, ‘Dr. Arturo’
, ‘Ortiz’
INTO “DoctorsList” VALUES
( 4
, ‘Dr. Samantha’
, ‘Fox’
INTO “DoctorsList” VALUES
( 5
, ‘Dr. Peter’
, ‘East’

8.) Suppose your boss asks you to print the output of all doctor names from the database, but without the doctor initial “Dr.” in the first name. For example if the name of the doctor is Dr. Meri Keller, the report output should only be “Meri Keller”. This is a sample SQL query using substr you could use to achieve this:

SELECT substr(“FirstName”,5,20) FIRSTNAME,”LastName” LASTNAME from “DoctorsList”;

This is the output of this query:

Try to experiment with the above SQL query by changing some of its syntax and checking the output.

{mospagebreak title=Oracle instr() and replace() Function}

This function is used to search a specific character/string within the string and return the location. This is the syntax:

instr(givenstring, stringchartosearch,start_position,nth_appearance)

Suppose you are given this string: Hello World

If you want to find the location of the letter “o” and start searching from the first character you would use this code:

instr(“Hello World”,’o',1)

This will return 5 since the letter 0 is located in the fifth character. However if use the nth appearance and need to use the location of the second occurrence:

instr(“Hello World”,’o',2)

This will return 8 since the second occurrence of the letter 0 is in the 8th character of the string.

Let’s have an actual Oracle SQL query that use this function. Supposing you will use the DoctorsList table in the previous section and you are asked to print the complete list of Doctors name with letter “e” anywhere in the FirstName, you could use this SQL query:

SELECT * FROM “DoctorsList” where INSTR(“FirstName”, ‘e’) > 0;

This is the output:

ID    FirstName    LastName

1    Dr. Meri    Keller

5    Dr. Peter    East

The condition > 0 will test if FirstName contains letter “e”; it will return 0 if it is not found. The first name “Meri” and “Peter” both contain letter e.

Note: This is a case-sensitive search. So if capital letter “E” is used in the above example instead of “e” then no data will be returned.

Oracle replace() Function

This function will replace a sequence of characters in the given string with another set of characters. This is the syntax:

replace(givenstring,stringtoreplace, replacementstring)

If replacementstring is omitted, the function will simply remove/filter the stringtoreplace from the given string. For example if this is the syntax:

replace(“hello world”,’world’)

It will return only “hello”.

Another example is that if the given string is: “Hello World” and you need to change it to “Hello Codex”, then you now need to include the stringtoreplace:

replace(“Hello World”,’World’,'Codex’)

A simple way of removing the “Dr.” initial from the DoctorsList example is by using the replace function. For example:

SELECT replace(“FirstName”,’Dr.’) FIRSTNAME,”LastName” LASTNAME from “DoctorsList”;

This will give you the same result as the substr example illustrated previously.

{mospagebreak title=Oracle trim() and lpad() Function}

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:

lpad(givenstring,paddinglength, paddedstring)

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

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

{mospagebreak title=Oracle concat(), length(), translate() Functions}

This is a simple string function that will combine two strings. Syntax:

concat(string1, string2)

Supposing you are asked to print the Doctors first and last name in the DoctorsList table and you need to add “MD” at the end of the lastname to signify Doctor of Medicine, this is the SQL query you would use:

SELECT “FirstName” FIRSTNAME,concat(“LastName”,’, MD’) LASTNAME from “DoctorsList”;

This is the output:

Dr. Meri        Keller, MD
Dr. John        Know, MD
Dr. Arturo     Ortiz, MD
Dr. Samantha Fox, MD
Dr. Peter     East, MD

Oracle translate() Function

A simple way of replacing any sequence of characters is using the translate function. You do not need to deal with the number of characters in your given string. All you need to define is string to replace and your replacement string. Syntax:


For example, supposing you are asked to print an output of Doctors First and Last name but you are asked to remove the Doctors initial and replace it with “Mr.”

This is the SQL query:

SELECT translate(“FirstName”,’Dr.’,'Mr.’) FIRSTNAME,”LastName” LASTNAME from “DoctorsList”;

This is output of this query:

Mr. Meri       Keller
Mr. John       Know
Mr. Arturo    Ortiz

Oracle length() Function

This is a very simple function to return the number of characters of a given string. Syntax:


You can use this function along with other Oracle string functions that needs the string length data such as the substr and lpad function.

For example in the substr example to remove the “Dr.” initial from the first name:

SELECT substr(“FirstName”,5,20) FIRSTNAME,”LastName” LASTNAME from “DoctorsList”;

The length of the substr which is 20 is estimated only and does not depend on the FirstName character length. The downside of the above query is that if the first name is more than 20 characters, then it won’t be properly displayed on the report because it will be truncated.

The solution is to use the length string function that count the number of characters for the string. The appropriate query would be:

SELECT substr(“FirstName”,5,length(“FirstName”)-3) FIRSTNAME,”LastName” LASTNAME from “DoctorsList”;

This is the output:

Meri        Keller
John        Know
Arturo     Ortiz
Samantha  Fox
Peter        East

Using the above technique, regardless of the FirstName number of characters it would still display correctly since substr length does not depend on single figure but varies depending on the FirstName length.

Google+ Comments

Google+ Comments