Oracle instr() and replace() Function - Oracle

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