The Complete Guide to SQL LIKE Operator

1. What is the SQL LIKE Operator?

The SQL LIKE operator is a powerful tool used for pattern matching in database queries. Unlike exact matching with =, which requires a perfect character-for-character match, LIKE allows flexible searching using wildcards to find partial matches.

For example, if you want to find all customers whose names start with “Joh”, you wouldn’t know if it’s “John,” “Johanna,” or “Johannes.” With LIKE, you can search for 'Joh%' to capture all variations.

Key Features:

✔ Searches for partial matches (not just exact strings)
✔ Works with text-based columns (VARCHAR, TEXT, etc.)
✔ Case-insensitive by default in most databases (but can be made case-sensitive)
✔ Used in WHERE clauses to filter results

For foundational SQL knowledge, see our beginner’s guide to SQL.

Why Use LIKE Instead of Exact Matching?

Exact matching (=) works when you know the precise value you’re looking for. But in real-world scenarios, you often need fuzzy matching—where only part of the string is known.

When to Use LIKE Over =

✅ Use LIKE when:

• You need partial matches (prefix, suffix, or substring).
• You’re dealing with user-generated search inputs (e.g., autocomplete).
• You want to filter data dynamically without knowing the full value.

❌ Stick to = when:

• You need exact matches (faster performance).
• You’re comparing numeric or non-text data.

Basic Syntax and Structure

The general syntax for LIKE is:

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

Example Queries

1. Find names starting with “A”:

   SELECT * FROM employees WHERE name LIKE 'A%';

→ Returns “Alice,” “Aaron,” but not “Bob.”

2. Find names ending with “son”:

   SELECT * FROM employees WHERE name LIKE '%son';

→ Returns “Johnson,” “Wilson,” but not “Smith.”

3. Find names containing “ana”:

   SELECT * FROM employees WHERE name LIKE '%ana%';

→ Returns “Diana,” “Banana,” but not “John.”

2. Wildcards in SQL LIKE

Wildcards are special characters that define how flexible the pattern matching should be.

The % Wildcard (Zero or More Characters)

• Represents any sequence of characters (including none).
• Example:
• 'A%' → Matches “Apple,” “A,” “Antelope”
• '%z%' → Matches “Zoo,” “Pizza,” “Lazy”
• '%ing' → Matches “Running,” “Sing,” but not “Ingot”

Use Cases:

✔ Prefix search ('Joh%' → “John,” “Johanna”)
✔ Suffix search ('%.com' → Finds all email domains)
✔ Substring search ('%error%' → Finds log entries with “error”)

The _ Wildcard (Single Character)

• Represents exactly one character (any character).
• Example:
• 'h_t' → Matches “hot,” “hat,” but not “hoot”
• 'J_n' → Matches “Jen,” “Jon,” but not “Jane”
• 'A__' → Matches any 3-letter word starting with “A” (e.g., “Ant,” “Ape”)

Use Cases:

✔ Fixed-length pattern matching (e.g., product codes like A1_4X).
✔ Finding typos (e.g., 'Gr_y' matches “Gray” or “Grey”).

Combining Wildcards for Advanced Patterns

You can mix % and _ for more precise searches.

Examples:

1. Find names where the 2nd letter is “a”:

   SELECT * FROM users WHERE name LIKE '_a%';

→ “Sam,” “Laura,” but not “Bob.”

2. Find 5-letter words starting with “S” and ending with “y”:

   SELECT * FROM words WHERE word LIKE 'S___y';

→ “Sunny,” “Silly,” but not “Sunshine.”

3. Find emails with a 3-letter domain prefix (e.g., “gmail”):

   SELECT * FROM emails WHERE email LIKE '%@___%.com';

→ Matches “test@gmail.com,” but not “test@yahoo.com.”

Database-Specific Wildcards (e.g., [] in SQL Server)

Some databases offer extra wildcards for advanced matching:

Example in SQL Server:

-- Find names starting with A, B, or C  
SELECT * FROM customers WHERE name LIKE '[ABC]%';

⚠ Note: MySQL and PostgreSQL do not support [] wildcards—use REGEXP instead.

3. Case Sensitivity in LIKE Queries

Default Case Sensitivity Across Databases

The behavior of LIKE regarding case sensitivity varies significantly across database systems:

MySQL

• Default: Case-insensitive for most collations (e.g., utf8_general_ci)
• Example: WHERE name LIKE 'john%' matches “John”, “JOHN”, and “john”
• Exception: When using binary collations (utf8_bin), becomes case-sensitive

PostgreSQL

• Default: Case-sensitive (LIKE 'John%' only matches “John”, not “JOHN”)
• Case-insensitive alternative: ILIKE operator

SQL Server

• Default: Depends on collation setting
• Common collations:
• SQL_Latin1_General_CP1_CI_AS (case-insensitive)
• SQL_Latin1_General_CP1_CS_AS (case-sensitive)

Oracle

• Default: Case-sensitive (LIKE 'John%' only matches exact case)
• Must use UPPER() or LOWER() functions for case-insensitive searches

Making LIKE Case-Sensitive

MySQL: Using BINARY Keyword

SELECT * FROM users 
WHERE BINARY username LIKE 'Admin%';

• Forces byte-by-byte comparison
• Only matches “Admin”, not “admin” or “ADMIN”

PostgreSQL/SQL Server

-- PostgreSQL
SELECT * FROM users 
WHERE username LIKE 'Admin%' COLLATE "C";

-- SQL Server
SELECT * FROM users 
WHERE username LIKE 'Admin%' COLLATE SQL_Latin1_General_CP1_CS_AS;

Using ILIKE (PostgreSQL/Redshift)

PostgreSQL and Amazon Redshift provide ILIKE for case-insensitive matching:

SELECT * FROM products 
WHERE name ILIKE 'apple%';

• Matches “Apple”, “APPLE”, “aPpLe”
• More readable than LOWER(name) LIKE 'apple%'
• Note: Not available in MySQL, SQL Server, or Oracle

4. Common SQL LIKE Examples

Starts With (LIKE ‘A%’)

Finds all records where a column value begins with specified characters.

Example: Find employees whose last names start with “Sm”

SELECT * FROM employees 
WHERE last_name LIKE 'Sm%';

• Matches: “Smith”, “Smythe”
• Doesn’t match: “Anderson”, “smiley” (unless case-insensitive)

Ends With (LIKE ‘%Z’)

Finds all records where a column value ends with specified characters.

Example: Find products with codes ending in “X”

SELECT * FROM products 
WHERE product_code LIKE '%X';

• Matches: “123X”, “SpecialX”
• Doesn’t match: “X123”, “Extra”

Contains a Substring (LIKE ‘%abc%’)

Finds records containing a specific sequence anywhere in the string.

Example: Find all error logs containing “timeout”

SELECT * FROM server_logs 
WHERE message LIKE '%timeout%';

• Matches: “Connection timeout”, “Timeout error occurred”
• Doesn’t match: “Timed out” (unless using LIKE '%time%out%')

Exact Length Matching (LIKE ‘_‘)

Using underscores to match specific string lengths.

Example: Find all 4-letter usernames

SELECT * FROM users 
WHERE username LIKE '____';

• Matches: “John”, “Anna”
• Doesn’t match: “Chris”, “Sam”

Example: Find 5-digit ZIP codes

SELECT * FROM addresses 
WHERE zip LIKE '_____' 
AND zip NOT LIKE '%[^0-9]%';

Combining Multiple Conditions

Use OR to match multiple patterns or AND for stricter matching.

Example: Find names starting with A or B

SELECT * FROM customers 
WHERE name LIKE 'A%' OR name LIKE 'B%';

Example: Find emails from Gmail or Yahoo

SELECT * FROM users 
WHERE email LIKE '%@gmail.com' 
OR email LIKE '%@yahoo.com';

Example: Strict pattern combining AND

SELECT * FROM products 
WHERE name LIKE 'A%' 
AND name LIKE '%Z';

• Finds names starting with A AND ending with Z
• Matches: “AmazingZ”, “AtoZ”
• Doesn’t match: “Alpha”, “Zebra”

5. Advanced LIKE Techniques

Using NOT LIKE for Exclusion

The NOT LIKE operator excludes records that match a specified pattern, effectively working as a negative filter.

Example: Find all products except those starting with “TEST”

SELECT * FROM products 
WHERE product_name NOT LIKE 'TEST%';

Real-world use case: Filtering out test data

SELECT user_email FROM registrations
WHERE user_email NOT LIKE '%@test.com'
AND user_email NOT LIKE 'temp%@%';

Key considerations:

• NOT LIKE is functionally equivalent to NOT (column LIKE pattern)
• Performance impact is similar to LIKE – both require full scans when using leading wildcards
• Can be combined with other conditions using AND

LIKE with Multiple Columns

Combining LIKE conditions across multiple columns enables powerful multi-criteria pattern matching.

Example: Find employees with first name starting with “J” and last name containing “son”

SELECT * FROM employees
WHERE first_name LIKE 'J%'
AND last_name LIKE '%son%';

Advanced example: Search across multiple fields

SELECT * FROM contacts
WHERE (first_name LIKE '%john%' OR 
       last_name LIKE '%john%' OR 
       email LIKE '%john%')
AND phone LIKE '617%';

Optimization tip:
Place the most selective condition first to reduce the dataset early in query execution.

Dynamic Pattern Building

Creating patterns dynamically allows for flexible searches based on variables or other column values.

Example: Find similar product names

SELECT p2.product_name
FROM products p1, products p2
WHERE p1.product_id = 123
AND p2.product_name LIKE CONCAT('%', SUBSTRING(p1.product_name, 2, 3), '%')
AND p2.product_id != p1.product_id;

Using variables in stored procedures:

CREATE PROCEDURE search_customers(IN pattern VARCHAR(100))
BEGIN
    SELECT * FROM customers
    WHERE customer_name LIKE CONCAT('%', pattern, '%');
END;

Escaping Wildcards

To search for literal % or _ characters, you need to escape them using the ESCAPE clause.

Standard syntax:

SELECT * FROM documents
WHERE content LIKE '%25\% discount%' ESCAPE '\';

Database-specific examples:

• MySQL/PostgreSQL:

  SELECT * FROM logs WHERE message LIKE '%\%%' ESCAPE '\';

• SQL Server (alternative):

  SELECT * FROM logs WHERE message LIKE '%[%]%';

Practical example: Finding audit records with percentage values

SELECT * FROM financial_transactions
WHERE notes LIKE '%\%%' ESCAPE '\'
AND notes LIKE '%increase%';

6. Performance Optimization with LIKE

Impact of Wildcard Placement

The position of wildcards significantly affects query performance:

Best case (index-friendly):

WHERE column LIKE 'prefix%'

• Uses index for the initial fixed portion
• Fast execution even on large tables

Worst case (full scan required):

WHERE column LIKE '%suffix'

• Cannot use standard B-tree indexes
• Requires full table scan

Mixed case:

WHERE column LIKE '%infix%'

• Worst performance profile
• Scans entire table and checks every row

Index Usage and LIKE

Understanding how indexes work with LIKE is crucial for performance:

Index-friendly patterns:

• 'prefix%' – Can use index range scan
• 'fixed_' – May use index for exact length matches

Non-sargable patterns:

• '%suffix' – Cannot use index
• '%infix%' – Cannot use index
• '_prefix%' – Leading wildcard prevents index use

Example of index usage:

-- Creates an index that can be used for prefix searches
CREATE INDEX idx_customer_name ON customers(last_name);

-- This query can utilize the index
SELECT * FROM customers WHERE last_name LIKE 'Smith%';

Alternatives for Faster Searches

Full-Text Search

When dealing with large text documents or complex searches:

MySQL example:

ALTER TABLE articles ADD FULLTEXT(title, body);

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization');

PostgreSQL example:

CREATE EXTENSION pg_trgm;

SELECT * FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('optimize & perform');

Trigram Indexes (PostgreSQL)

For advanced pattern matching with wildcards:

CREATE EXTENSION pg_trgm;
CREATE INDEX trgm_idx ON customers USING gin(last_name gin_trgm_ops);

SELECT * FROM customers 
WHERE last_name LIKE '%son%';  -- Can use trigram index

Materialized Patterns

For frequently searched patterns:

-- Add computed column
ALTER TABLE products ADD COLUMN search_pattern VARCHAR(10) 
GENERATED ALWAYS AS (SUBSTRING(product_name, 1, 3)) STORED;

-- Create index
CREATE INDEX idx_product_pattern ON products(search_pattern);

-- Query using the pre-computed pattern
SELECT * FROM products WHERE search_pattern LIKE 'A1%';

Performance comparison table:

Key optimization strategies:

1. Avoid leading wildcards when possible
2. Consider computed columns for common search patterns
3. Use specialized indexes (trigram, full-text) for complex searches
4. For suffix searches, store reversed strings with indexes
5. Partition tables by search patterns when applicable

7. LIKE vs. Other SQL Pattern Matching Methods

LIKE vs. REGEXP/RLIKE

When to Use Each Method

Example: Find phone numbers

-- LIKE approach (simple patterns)
SELECT * FROM contacts 
WHERE phone LIKE '617%' OR phone LIKE '857%';

-- REGEXP approach (complex validation)
SELECT * FROM contacts
WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

When to choose REGEXP:

• Validating email/phone formats
• Extracting subpatterns (capture groups)
• Complex alternations (OR conditions)

LIKE vs. SUBSTRING/POSITION

Alternative Approaches for Specific Needs

Performance Note:

• POSITION()/INSTR() often outperforms LIKE '%substr%'
• SUBSTRING with exact positions is faster than multiple _ wildcards

Example: Find 2nd character ‘A’

-- LIKE approach
SELECT * FROM products WHERE name LIKE '_A%';

-- SUBSTRING approach (better performance)
SELECT * FROM products WHERE SUBSTRING(name, 2, 1) = 'A';

LIKE vs. Full-Text Search

Choosing the Right Tool for Text Search

When to Upgrade to Full-Text:

• Searching large documents (>1KB text)
• Needing relevance scoring
• Supporting multiple languages
• Implementing search-as-you-type

PostgreSQL Example:

-- Basic LIKE search
SELECT * FROM articles WHERE content LIKE '%database%';

-- Full-text alternative
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('database');

8. Database-Specific LIKE Behaviors

MySQL

Special Features

• BINARY LIKE: Forces case-sensitive comparison

  SELECT * FROM users WHERE BINARY username LIKE 'Admin%';

• REGEXP: Alternate regex syntax

  SELECT * FROM products WHERE name REGEXP '^[A-C]';

• Collation Control:

  SELECT * FROM table WHERE col LIKE 'a%' COLLATE utf8_bin;

PostgreSQL

Enhanced Pattern Matching

• ILIKE: Case-insensitive matching

  SELECT * FROM products WHERE name ILIKE 'apple%';

• Regex Operator (~):

  SELECT * FROM logs WHERE message ~ 'error:[0-9]{4}';

• Trigram Support:

  CREATE EXTENSION pg_trgm;
  SELECT * FROM docs WHERE content LIKE '%quick%'; -- Uses trigram index

SQL Server

Unique Wildcard Options

• Character Classes ([]):

  SELECT * FROM employees WHERE name LIKE '[A-C]%';

• PATINDEX: Pattern position function

  SELECT * FROM documents 
  WHERE PATINDEX('%[0-9][0-9][0-9]%', content) > 0;

• ESCAPE with Brackets:

  SELECT * FROM sales WHERE notes LIKE '%50[%] off%';

Oracle

Advanced Pattern Control

• REGEXP_LIKE: Comprehensive regex support

  SELECT * FROM contacts 
  WHERE REGEXP_LIKE(phone, '^\(\d{3}\) \d{3}-\d{4}$');

• ESCAPE Variations:

  SELECT * FROM products 
  WHERE description LIKE '%\%%' ESCAPE '\';

• NLS Parameters:

  ALTER SESSION SET NLS_COMP=LINGUISTIC;
  ALTER SESSION SET NLS_SORT=BINARY_CI;
  SELECT * FROM users WHERE name LIKE 'john%'; -- Now case-insensitive

Key Takeaways:

1. Each database offers unique extensions to the standard LIKE
2. Regex implementations vary significantly (syntax, performance)
3. Case handling requires database-specific approaches
4. Specialized functions (PATINDEX, REGEXP_LIKE) provide alternatives
5. Consider using database-specific features when portability isn’t required

9. Common Mistakes and Troubleshooting with SQL LIKE

Incorrect Wildcard Usage (Overusing %)

The Problem:

Excessive use of % wildcards, especially leading wildcards, is the #1 performance killer in LIKE queries:

-- Anti-pattern (forces full table scan)
SELECT * FROM products WHERE description LIKE '%organic%';

-- Better approach when possible
SELECT * FROM products 
WHERE description LIKE 'organic%'  -- Prefix search (index-friendly)
   OR description LIKE '% organic%';  -- Space-prefixed alternative

Solutions:

1. Avoid leading wildcards unless absolutely necessary
2. Combine with other filters to reduce dataset first:

   SELECT * FROM large_table
   WHERE date_added > '2023-01-01'
   AND content LIKE '%critical%';  -- Apply to smaller subset

3. Consider reverse indexing for suffix searches:

   -- Create reversed column
   ALTER TABLE products ADD COLUMN name_reversed VARCHAR(255);
   UPDATE products SET name_reversed = REVERSE(name);
   CREATE INDEX idx_reversed ON products(name_reversed);

   -- Fast suffix search
   SELECT * FROM products 
   WHERE name_reversed LIKE REVERSE('%berry');

Case Sensitivity Surprises

The Problem:

Inconsistent behavior across databases leads to unexpected mismatches:

-- MySQL (matches 'apple', 'Apple', 'APPLE')
SELECT * FROM fruits WHERE name LIKE 'apple%';

-- PostgreSQL (only matches 'apple')
SELECT * FROM fruits WHERE name LIKE 'apple%';

Solutions:

1. Explicitly declare case handling:

   -- MySQL (force case-sensitive)
   SELECT * FROM fruits WHERE BINARY name LIKE 'Apple%';

   -- PostgreSQL (force case-insensitive)
   SELECT * FROM fruits WHERE name ILIKE 'apple%';

2. Standardize data on insert/update:

   INSERT INTO products (name) VALUES (LOWER('Organic Apple'));

3. Use consistent collations:

   ALTER TABLE users MODIFY username VARCHAR(50) COLLATE utf8_bin;

NULL Handling Pitfalls

The Problem:

LIKE with NULL values returns NULL (not FALSE), causing unexpected exclusions:

-- Returns no rows if email is NULL
SELECT * FROM users WHERE email LIKE '%@gmail.com';

Solutions:

1. Explicit NULL handling:

   SELECT * FROM users 
   WHERE (email LIKE '%@gmail.com' OR email IS NULL);

2. COALESCE for default values:

   SELECT * FROM products 
   WHERE COALESCE(description, '') LIKE '%organic%';

3. Use IS NULL as separate condition in WHERE clauses

Performance Bottlenecks

Diagnosing Slow LIKE Queries

1. Check execution plans for full table scans

   EXPLAIN SELECT * FROM logs WHERE message LIKE '%error%';

2. Monitor query duration for patterns with leading wildcards
3. Identify worst-performing patterns:

   -- MySQL slow query log
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;

Optimization Techniques

1. Add computed columns for frequent search terms

   ALTER TABLE articles ADD COLUMN has_error BOOLEAN
   GENERATED ALWAYS AS (content LIKE '%error%') STORED;

2. Use partial indexes:

   CREATE INDEX idx_error_messages ON logs(message(100))
   WHERE message LIKE '%error%';

3. Consider external search (Elasticsearch, Algolia) for large text corpora

10. Security Considerations with LIKE

SQL Injection Risks

The Danger:

Unsanitized user input in LIKE patterns enables injection:

-- Vulnerable code (PHP example)
$query = "SELECT * FROM users WHERE name LIKE '%" . $_GET['search'] . "%'";
-- Attacker inputs: ' OR '1'='1' --

Exploit Scenarios:

1. Data exfiltration through boolean-based blind SQLi
2. Authentication bypass via crafted patterns
3. Denial of Service with expensive pattern matching

Parameterized Queries

Proper Implementation:

# Python (psycopg2)
cursor.execute(
    "SELECT * FROM products WHERE name LIKE %s",
    ('%' + user_input + '%',)
)

// Java (JDBC)
PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM logs WHERE message LIKE ?"
);
stmt.setString(1, "%" + userInput + "%");

Key Benefits:

1. Automatic escaping of special characters
2. Combine LIKE with WHEREPrevention of injection attacks
3. Better query plan caching

Input Sanitization Techniques

Defensive Measures:

1. Escape wildcards in user input:

   def safe_like(input):
       return input.replace('%', '\\%').replace('_', '\\_')

2. Length validation to prevent DoS:

   -- Reject patterns over 50 chars
   WHERE description LIKE :pattern AND LENGTH(:pattern) <= 50

3. Allowlist safe characters:

   if (!userInput.matches("[a-zA-Z0-9 ]+")) {
       throw new InvalidInputException();
   }

Advanced Protection:

1. Search-only database user with read-only permissions
2. Query timeouts to prevent long-running scans
3. Rate limiting search requests per user

Audit Checklist for Secure LIKE Queries

1. All LIKE patterns use parameterized queries
2. User input is validated before pattern construction
3. The database user has the least privileges needed
4. Monitoring for anomalous search patterns
5. Regular security testing of search endpoints

Remember: Even LIKE Clauses need the same security precautions as other SQL fragments when handling user input. The convenience of wildcards shouldn’t compromise security.

11. Real-World Use Cases for SQL LIKE

Data Cleaning with LIKE

Finding Inconsistent Data Entries

LIKE is invaluable for identifying and standardizing messy data:

-- Find phone numbers with inconsistent formatting
SELECT customer_id, phone 
FROM contacts
WHERE phone NOT LIKE '+%' 
  AND phone NOT LIKE '__-___-____'
  AND phone NOT LIKE '(%___%) %___-____';

Common cleaning scenarios:

1. Standardizing product codes

   UPDATE products
   SET sku = REGEXP_REPLACE(sku, '[^A-Z0-9]', '')
   WHERE sku LIKE '%[^A-Z0-9]%';

2. Identifying email anomalies

   SELECT email 
   FROM users
   WHERE email NOT LIKE '_%@_%._%'
     AND email NOT LIKE '%@%.%';

3. Detecting placeholder values

   SELECT * FROM orders
   WHERE customer_name LIKE '%TEST%' 
      OR customer_name LIKE '%DUMMY%'
      OR customer_name LIKE '%-%-%';

Search Functionality Implementation

Autocomplete & Partial Matching

LIKE powers responsive search experiences:

-- Basic autocomplete
SELECT product_name 
FROM products
WHERE product_name LIKE 'Appl%'
ORDER BY product_name
LIMIT 10;

-- Weighted search (priority to exact matches)
(SELECT product_name, 1 AS priority FROM products WHERE product_name = 'Apple')
UNION ALL
(SELECT product_name, 2 FROM products WHERE product_name LIKE 'Apple %')
UNION ALL
(SELECT product_name, 3 FROM products WHERE product_name LIKE '%Apple%')
ORDER BY priority, product_name;

Use LIMIT to constrain pattern-matched results.

Advanced techniques:

• Prefix indexing for instant results
• Caching frequent searches
• Hybrid approaches combining LIKE with full-text search

Fuzzy Matching

-- Find similar names with typos
SELECT * FROM employees 
WHERE last_name LIKE 'Sm_th' 
   OR last_name LIKE 'Smi_h'
   OR last_name LIKE 'Smith_';

Log Analysis with LIKE

Pattern Filtering in System Logs

-- Find error sequences
SELECT timestamp, message 
FROM system_logs
WHERE message LIKE '%ERROR%:%'
  AND message NOT LIKE '%expected%ERROR%'
ORDER BY timestamp DESC;

Common log patterns:

1. Session tracking

   SELECT * FROM auth_logs
   WHERE message LIKE 'User%session%timeout%';

2. Performance bottlenecks

   SELECT AVG(duration) 
   FROM api_logs 
   WHERE path LIKE '%/reports/%'
     AND message LIKE '%>500ms%';

3. Security monitoring

   SELECT COUNT(*) 
   FROM access_logs
   WHERE path LIKE '%/admin%'
     AND user_agent NOT LIKE '%CompanyBrowser%';

12. Best Practices for Efficient LIKE Queries

1. Avoid Leading Wildcards

Performance Impact Comparison

Workaround for suffix searches:

-- Add reversed column with index
ALTER TABLE products ADD COLUMN name_reversed VARCHAR(255) AS (REVERSE(name)) STORED;
CREATE INDEX idx_name_reversed ON products(name_reversed);

-- Fast suffix search
SELECT * FROM products 
WHERE name_reversed LIKE REVERSE('%berry');

2. Combine with Other Filters

Query Optimization Strategy

-- Before (slow)
SELECT * FROM sales 
WHERE notes LIKE '%discount%';

-- After (optimized)
SELECT * FROM sales
WHERE sale_date > CURRENT_DATE - INTERVAL 30 DAY
  AND notes LIKE '%discount%';  -- Applied to smaller dataset

Filter ordering matters:

1. Equality conditions first (status = 'active')
2. Range filters next (date BETWEEN ...)
3. LIKE conditions last

3. Strategic Index Usage

Effective Indexing Approaches

Prefix indexes for LIKE:

-- MySQL
ALTER TABLE customers ADD INDEX idx_last_name_prefix (last_name(5));

-- PostgreSQL
CREATE INDEX idx_name_trgm ON customers USING gin (last_name gin_trgm_ops);

Covering indexes:

CREATE INDEX idx_cover ON products(product_name, price, stock)
WHERE product_name LIKE 'A%';

Index selection guidelines:

• Columns used in LIKE 'prefix%' conditions
• Frequently searched columns
• Columns with high selectivity

4. Pattern Documentation

Maintainable Pattern Examples

-- Product search patterns (2024-05 schema)
-- PAT001: Standard product codes (AA-9999)
WHERE sku LIKE '__-____'

-- PAT002: Legacy product references (X followed by 5 digits)
WHERE ref LIKE 'X_____'

-- PAT003: Temporary SKUs (TEMP followed by date)
WHERE sku LIKE 'TEMP202%'

Pattern documentation should include:

1. Business purpose of the pattern
2. Expected match examples
3. Performance characteristics
4. Schema dependencies

Version-controlled pattern library:

## Product Matching Patterns
| PatternID | Description          | Example        | Performance |
|-----------|----------------------|----------------|-------------|
| PROD-001  | New SKU format       | 'NW-2024-%%%%' | Fast (indexed) |
| PROD-002  | Legacy codes         | 'LC_%%%%'      | Medium       |

Additional Best Practices:

• Use COLLATE consistently across environments
• Monitor slow queries with LIKE operations
• Consider materialized views for complex searches
• Test edge cases (empty strings, NULL values)
• Review query plans regularly for LIKE usage

By implementing these practices, you maintain query performance while keeping your pattern-matching logic transparent and maintainable for future development.

Conclusion

The SQL LIKE operator is an indispensable tool for flexible text searching in databases, enabling everything from simple prefix matching to complex pattern-based queries. Throughout this guide, we’ve explored its core functionality—wildcards (%, _), case sensitivity handling, and performance optimization—while highlighting practical applications in data cleaning, search functionality, and log analysis.

Key Takeaways

1. Wildcards are powerful but require discipline—leading % wildcards cripple performance, while strategic use of _ enables precise fixed-length matching.
2. Case sensitivity varies by database—use BINARY, COLLATE, or ILIKE (PostgreSQL) to control matching behavior explicitly.
3. Security is non-negotiable—always parameterize LIKE clauses to prevent SQL injection, and sanitize user input rigorously.
4. Alternatives exist for scale—consider full-text search or regex for complex patterns, and trigram indexes (PostgreSQL) for advanced wildcard performance.
5. Real-world usage demands balance—combine LIKE with other filters, document patterns for maintainability, and monitor slow queries proactively.

Final Recommendation

Use LIKE judiciously:

• For simple patterns (prefix/suffix matches), it’s efficient and readable.
• For complex text searches, transition to dedicated tools (Elasticsearch, full-text search).
• Always prioritize clarity and security over clever pattern tricks.

By applying these principles, you’ll harness LIKE’s flexibility while avoiding its pitfalls—ensuring fast, secure, and maintainable pattern matching in your SQL workflows.

Next Steps: Experiment with your database’s unique LIKE optimizations (e.g., PostgreSQL’s pg_trgm), and benchmark alternatives for critical search paths. Happy querying!