The % Wildcard: Match Any Number of Characters

The percent sign (%) represents zero, one, or multiple characters in a LIKE pattern.

The _ Wildcard: Match a Single Character

The underscore (_) represents exactly one character in a LIKE pattern.

Combining % and _ Wildcards

You can use both wildcards together for more complex pattern matching.

Prefix Matching (Starts With)

Find strings that start with a specific pattern.

Performance Note: Prefix matching (pattern%) can utilize indexes and is generally more efficient than patterns starting with wildcards.

Suffix Matching (Ends With)

Find strings that end with a specific pattern.

Substring Matching (Contains)

Find strings that contain a specific pattern anywhere.

Case Sensitivity: LIKE is typically case-sensitive in most databases. Use ILIKE in PostgreSQL or UPPER()/LOWER() functions for case-insensitive matching.

Pattern Escaping

When you need to search for the wildcard characters themselves (% or _), use the ESCAPE clause.

Note: The exact escape syntax varies slightly between database systems. Check your specific database's documentation.

Case-Insensitive Matching

Perform case-insensitive pattern matching using database-specific features.

Understanding the ILIKE Operator

The ILIKE operator is a PostgreSQL-specific extension that performs case-insensitive matching. It works exactly like LIKE but ignores the case of letters.

• ILIKE uses the same wildcard patterns as LIKE (% and _)
• It's only available in PostgreSQL and some PostgreSQL-compatible databases
• It's often more readable and potentially more efficient than using LOWER() or UPPER()

Note: For cross-database compatibility, using LOWER() or UPPER() with LIKE is recommended over ILIKE when your application might run on different database systems.

NOT LIKE for Exclusion

Exclude rows that match a specific pattern.

Multiple Pattern Conditions

Combine multiple LIKE conditions using AND or OR.

Example 1: Content Search

This example shows how to search for videos based on title patterns:

Example 2: User Search

This example demonstrates searching for users based on username or email patterns:

Example 3: Advanced Pattern Searching

This example shows more complex pattern searching with combined techniques:

1. Leading Wildcard Caution

Patterns that start with a wildcard ('%pattern' or '_pattern') typically cannot use indexes efficiently, which can lead to full table scans and poor performance on large tables.

2. Consider Full-Text Search for Complex Searches

For more advanced text searching needs, consider using your database's full-text search capabilities instead of LIKE, especially for large text columns or complex search requirements.

3. Pattern Specificity

More specific patterns (those with fewer wildcards or wildcards in specific positions) generally perform better than very general patterns.

4. Alternative Indexing Strategies

Some databases support special indexes for LIKE operations, especially for prefix searches. Check your database documentation for these optimizations.