Use string contains function in oracle SQL query
Mastering String Contains in Oracle SQL Queries

Learn how to effectively use string containment functions like LIKE
, INSTR
, and REGEXP_LIKE
in Oracle SQL to search for substrings within your data.
Searching for specific substrings within larger text fields is a common requirement in database operations. Oracle SQL provides several powerful functions to achieve this, each with its own strengths and use cases. This article will guide you through the most popular methods for checking if a string 'contains' another string in Oracle SQL, from simple pattern matching to advanced regular expressions.
The LIKE
Operator: Basic Pattern Matching
The LIKE
operator is the most straightforward and frequently used method for pattern matching in SQL. It allows you to search for a specified pattern in a column. The two main wildcard characters used with LIKE
are:
%
: Represents zero or more characters._
: Represents a single character.
LIKE
is case-sensitive by default unless your database or session settings dictate otherwise (e.g., NLS_COMP
set to LINGUISTIC
and NLS_SORT
to BINARY_CI
or GENERIC_M_CI
). For case-insensitive searches, you can use LOWER()
or UPPER()
functions.
SELECT product_name
FROM products
WHERE product_name LIKE '%Laptop%';
Using LIKE
to find product names containing 'Laptop'.
SELECT product_name
FROM products
WHERE LOWER(product_name) LIKE '%laptop%';
Case-insensitive search using LOWER()
with LIKE
.
LOWER(column_name)
if you frequently perform case-insensitive LIKE
searches starting with a wildcard (e.g., %keyword
).The INSTR
Function: Finding Substring Position
The INSTR
function (In String) returns the starting position of the nth occurrence of a substring within a string. If the substring is not found, INSTR
returns 0. This makes it an excellent tool for checking for containment: if INSTR
returns a value greater than 0, the substring exists.
The basic syntax is INSTR(string, substring, [position], [occurrence])
:
string
: The string to search.substring
: The substring to search for.position
(optional): The position instring
where the search starts (default is 1). A negative value means searching backward from the end.occurrence
(optional): The nth occurrence ofsubstring
to search for (default is 1).
SELECT description
FROM articles
WHERE INSTR(description, 'database') > 0;
Using INSTR
to find descriptions containing 'database'.
SELECT description
FROM articles
WHERE INSTR(LOWER(description), 'database') > 0;
Case-insensitive search using INSTR
with LOWER()
.
flowchart TD A[Start Query] --> B{Check INSTR(column, 'substring') > 0?} B -- Yes --> C[Substring Found] B -- No --> D[Substring Not Found] C --> E[Return Row] D --> F[Skip Row] E --> G[End Query] F --> G
Flowchart illustrating the logic of using INSTR
for string containment.
The REGEXP_LIKE
Operator: Advanced Regular Expressions
For more complex pattern matching, Oracle's REGEXP_LIKE
operator is the most powerful option. It allows you to use regular expressions (regex) to define sophisticated search patterns. This is particularly useful when LIKE
's simple wildcards are insufficient.
The basic syntax is REGEXP_LIKE(source_string, pattern, [match_parameter])
:
source_string
: The string to search.pattern
: The regular expression pattern.match_parameter
(optional): A string of characters that modifies the matching behavior (e.g., 'i' for case-insensitive, 'c' for case-sensitive, 'm' for multi-line matching).
SELECT email_address
FROM users
WHERE REGEXP_LIKE(email_address, '@(gmail|yahoo)\.com');
Using REGEXP_LIKE
to find email addresses from Gmail or Yahoo domains.
SELECT comments
FROM feedback
WHERE REGEXP_LIKE(comments, 'error|issue|problem', 'i');
Case-insensitive search for multiple keywords using REGEXP_LIKE
.
REGEXP_LIKE
is powerful, it can be less performant than LIKE
or INSTR
for simple substring searches due to the overhead of regular expression processing. Use it when the complexity of the pattern truly requires it.