Are you looking to find specific words or pieces of text inside a longer text in Oracle SQL? Well, you're in the right place! In this quick tutorial, we'll show you how to use Oracle SQL to search for and work with those bits of text hidden within your data.

Here are examples of Oracle SQL queries to find substring in a string.

Find Substring in a String Using INSTR() Function Examples

In Oracle, the instr() function returns the position of a substring in the string. So if the substring exists in the string, it will return its position; if not exist, then it returns 0. Below is an example:

select instr('Oracle SQL Query', 'SQL') from dual;

Output:

8

Check another example below. It will return 0 because the searched string is not found in the string:

select instr('Oracle SQL Query', 'Select') from dual;

Output:

0

Suppose you want to get the data from a table where a particular column contains a specific string. Below is an example:

select * from employees
   where instr(emp_name, 'Mr.') > 0;

FAQ