HomeOracle 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.
If you want to find the location of the letter "o" and start searching from the first character you would use this code:
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:
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: