Skip to main content

Like operator with indexes in SQL

· 2 min read
Link Nuis
Link Nuis
Java Developer

Introduction​

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a cloumn. The performance and behavior of the LIKE operator in SQL change significantly when used alongside indexes.

1. Basic Syntax​

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

2. Wildcard Characters​

The LIKE operator uses two main wildcards to define patterns:

  • % (Percent sign): Represents zero, one, or multiple characters.
  • _ (Underscore): Represents a single character.
PatternDescriptionExample
a%Starts with 'a'apple, ant
%aEnds with 'a'banana, data
%or%Contains 'or' at any positionorange, world
_r%Has 'r' in the second positionarea, oracle
a__%Starts with 'a' and is at least 3 characters longabc, anchor
a%oStarts with 'a' and ends with 'o'audio, alumno

3. Advanced Usage​

  • NOT LIKE: Used to exclude rows that match a specific pattern.
    • Example: WHERE name NOT LIKE 'A%' (finds names not starting with A).
  • ESCAPE Clause: To search for literal % or _ characters, you must use an escape charater.
    • Example: WHERE discount LIKE '10!%' ESCAPE '!' finds values that literally contain '10%'.
  • Case Sensitive: By default, LIKE behavior depends on the database collation. In SQL Server, it is often case-insensitive, while in PostgreSQL, it is case-sensitive (use ILIKE for case-insensitive).

4. LIKE WITH INDEXES​

Standard B-Tree indexes only optimize LIKE queries if the search pattern is left-anchored (starts with constant charaters).

  • Index-friendly (prefix%): A query like WHERE name LIKE 'abc%' allows the database to perform an Index Seek. It treats the prefix as a range.
  • Index-Hostile (%suffix or %middle%): Patterns starting with a wildcard, like WHERE name LIKE '%abc', usually force a Full Table Scan or Full Index Scan because the engine cannot determine a starting point in the sorted index.
  • Optimize Tip: For non-prefix searches, consider using PostgeSQL Trigram GIN indexes or Full-Text Search features available in mordern RDBMS.