Use string contains function in oracle SQL query

Learn use string contains function in oracle sql query with practical examples, diagrams, and best practices. Covers sql, oracle-database development techniques with visual explanations.

Mastering String Contains in Oracle SQL Queries

Hero image for Use string contains function in oracle SQL query

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.

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 in string where the search starts (default is 1). A negative value means searching backward from the end.
  • occurrence (optional): The nth occurrence of substring 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.