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;
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;
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;
In computer programming and data processing, a "substring" is a contiguous sequence of characters within a longer string. It is essentially a portion or fragment of the original string that starts at a specified position (usually an index) and extends for a certain number of characters.
INSTR() function in Oracle SQL is used to find the position (or index) of a substring within a given string. It returns the position of the first occurrence of the substring within the string. If the substring is not found, it returns 0.
In Oracle SQL, you can find a substring within a string using several methods, including the
INSTR() function as discussed earlier.