Home arrow Oracle arrow Page 2 - Oracle String Functions with PL/SQL

Oracle instr() and replace() 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.

  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



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.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: