BLOG POSTS

MangoHost Blog / SQL LIKE and NOT LIKE – Pattern Matching in Queries

SQL LIKE and NOT LIKE – Pattern Matching in Queries

SQL LIKE and NOT LIKE operators are fundamental pattern matching tools that allow developers to search for specific patterns within text data using wildcards and character matching. These operators are essential for building flexible search functionality, data filtering, and content management systems, providing powerful alternatives to exact string matching. Throughout this guide, you’ll learn how to implement both operators effectively, understand their performance implications, master wildcard usage, and discover advanced pattern matching techniques that can enhance your database queries.

How LIKE and NOT LIKE Pattern Matching Works

The LIKE operator performs pattern matching using wildcards to find rows where column values match specific patterns. It supports two primary wildcards: the percent sign (%) matches zero or more characters, while the underscore (_) matches exactly one character. The NOT LIKE operator works inversely, returning rows that don’t match the specified pattern.

Here’s the basic syntax structure:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE 'pattern';

SELECT column_name(s)
FROM table_name
WHERE column_name NOT LIKE 'pattern';

The pattern matching is case-sensitive in most database systems, though this behavior varies by database engine and collation settings. When using LIKE, the database engine performs a sequential scan through the specified column unless appropriate indexes are in place.

Wildcard Characters and Pattern Examples

Understanding wildcard combinations is crucial for effective pattern matching. The percent (%) wildcard represents any sequence of characters, while underscore (_) represents a single character placeholder.

Pattern	Description	Example Matches	Won’t Match
‘john%’	Starts with “john”	john, johnson, johnsmith	ajohn, John
‘%smith’	Ends with “smith”	smith, johnsmith, asmith	smithy, Smith
‘%admin%’	Contains “admin”	admin, administrator, sysadmin	Admin, ADMIN
‘j_hn’	Four chars, j and hn with one between	john, jahn	johnson, jhn
‘___’	Exactly three characters	cat, dog, 123	cats, do

Step-by-Step Implementation Guide

Let’s work through practical implementations using a sample user database. First, create a test table with sample data:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(15),
    status VARCHAR(20)
);

INSERT INTO users VALUES
(1, 'john_doe', 'john.doe@email.com', '555-1234', 'active'),
(2, 'jane_smith', 'jane@company.org', '555-5678', 'inactive'),
(3, 'admin_user', 'admin@system.local', '555-9999', 'active'),
(4, 'test123', 'test@test.com', '555-0000', 'pending'),
(5, 'guest_account', 'guest@temp.net', NULL, 'active');

Now implement common search patterns:

-- Find users with email addresses from specific domains
SELECT username, email 
FROM users 
WHERE email LIKE '%.com';

-- Search for admin accounts
SELECT * 
FROM users 
WHERE username LIKE '%admin%';

-- Find phone numbers with specific area code
SELECT username, phone 
FROM users 
WHERE phone LIKE '555-%';

-- Exclude temporary or test accounts
SELECT * 
FROM users 
WHERE username NOT LIKE '%test%' 
AND username NOT LIKE '%temp%';

Advanced Pattern Matching Techniques

Beyond basic wildcards, you can combine multiple LIKE conditions and use them with other SQL operators for complex searches:

-- Multiple pattern matching with OR
SELECT * FROM users 
WHERE username LIKE '%admin%' 
   OR username LIKE '%manager%' 
   OR username LIKE '%supervisor%';

-- Combining LIKE with other conditions
SELECT * FROM users 
WHERE email LIKE '%.org' 
  AND status = 'active' 
  AND phone IS NOT NULL;

-- Case-insensitive search (MySQL/SQL Server)
SELECT * FROM users 
WHERE LOWER(username) LIKE LOWER('%ADMIN%');

-- Pattern matching with length constraints
SELECT * FROM users 
WHERE username LIKE '_____%'  -- At least 5 characters
  AND username NOT LIKE '%123%';

Performance Considerations and Optimization

LIKE operations can significantly impact query performance, especially with leading wildcards. Understanding performance implications helps optimize database operations:

Pattern Type	Index Usage	Performance	Recommendation
‘prefix%’	Can use index	Good	Preferred for searches
‘%suffix’	Cannot use index	Poor	Consider full-text search
‘%middle%’	Cannot use index	Poor	Use full-text search instead
‘_pattern’	Limited index use	Moderate	Use sparingly on large tables

To improve performance with pattern matching:

Create appropriate indexes for columns frequently used in LIKE queries
Avoid leading wildcards when possible
Consider full-text search indexes for complex text searching
Use LIMIT clauses to restrict result sets
Test query execution plans to identify bottlenecks

-- Create index for username searches
CREATE INDEX idx_username ON users(username);

-- Full-text index for better text searching (MySQL)
CREATE FULLTEXT INDEX idx_fulltext_username ON users(username);

-- Using full-text search instead of LIKE
SELECT * FROM users 
WHERE MATCH(username) AGAINST('admin' IN NATURAL LANGUAGE MODE);

Real-World Use Cases and Applications

Pattern matching with LIKE and NOT LIKE operators serves numerous practical applications across different scenarios:

User Management Systems:

-- Find all administrative accounts
SELECT username, last_login 
FROM users 
WHERE username LIKE '%admin%' 
   OR username LIKE '%root%' 
   OR username LIKE '%super%';

-- Exclude system accounts from user reports
SELECT * FROM users 
WHERE username NOT LIKE 'sys_%' 
  AND username NOT LIKE 'service_%' 
  AND username NOT LIKE '%system%';

Log Analysis and Monitoring:

-- Find error entries in log table
SELECT timestamp, message 
FROM application_logs 
WHERE message LIKE '%ERROR%' 
   OR message LIKE '%FATAL%' 
   OR message LIKE '%CRITICAL%';

-- Exclude debug messages from production logs
SELECT * FROM logs 
WHERE level NOT LIKE '%DEBUG%' 
  AND level NOT LIKE '%TRACE%';

E-commerce and Inventory:

-- Search products by partial name
SELECT product_name, price 
FROM products 
WHERE product_name LIKE '%laptop%' 
   OR product_name LIKE '%notebook%';

-- Find products with specific SKU patterns
SELECT * FROM inventory 
WHERE sku LIKE 'ELC-%' 
  AND sku NOT LIKE '%-DISC';

Database-Specific Implementations

Different database systems offer variations and extensions to standard LIKE functionality:

MySQL Specific Features:

-- Case-insensitive matching with COLLATE
SELECT * FROM users 
WHERE username LIKE '%admin%' COLLATE utf8_general_ci;

-- Regular expressions (more powerful than LIKE)
SELECT * FROM users 
WHERE username REGEXP '^[a-z]+_[a-z]+$';

PostgreSQL Extensions:

-- Case-insensitive ILIKE operator
SELECT * FROM users 
WHERE username ILIKE '%ADMIN%';

-- Similar to operator for fuzzy matching
SELECT * FROM users 
WHERE username SIMILAR TO '%(admin|root|super)%';

SQL Server Features:

-- Bracket expressions for character ranges
SELECT * FROM users 
WHERE username LIKE '[a-m]%';

-- Escape special characters
SELECT * FROM products 
WHERE description LIKE '%50!% off%' ESCAPE '!';

Common Pitfalls and Troubleshooting

Avoiding common mistakes ensures reliable pattern matching implementation:

Case Sensitivity Issues:

-- Problem: Case-sensitive search missing results
SELECT * FROM users WHERE username LIKE '%Admin%'; -- May miss 'admin'

-- Solution: Convert to consistent case
SELECT * FROM users WHERE LOWER(username) LIKE LOWER('%Admin%');

Special Character Handling:

-- Problem: Searching for literal wildcards
SELECT * FROM products 
WHERE code LIKE '50%'; -- Matches "50" followed by anything

-- Solution: Escape wildcards
SELECT * FROM products 
WHERE code LIKE '50\%' ESCAPE '\';

NULL Value Handling:

-- LIKE with NULL always returns NULL (unknown)
SELECT * FROM users 
WHERE phone LIKE '555-%'; -- Won't return rows with NULL phone

-- Include NULL handling explicitly
SELECT * FROM users 
WHERE phone LIKE '555-%' OR phone IS NULL;

Best Practices and Security Considerations

Implementing secure and efficient pattern matching requires attention to several key areas:

Always validate and sanitize user input before using in LIKE patterns
Use parameterized queries to prevent SQL injection attacks
Monitor query performance and optimize patterns for better execution
Consider database-specific features for enhanced functionality
Document complex pattern matching logic for team maintenance

-- Secure parameterized query example (pseudo-code)
PreparedStatement stmt = connection.prepareStatement(
    "SELECT * FROM users WHERE username LIKE ?"
);
stmt.setString(1, "%" + sanitizedInput + "%");
ResultSet rs = stmt.executeQuery();

For applications requiring high-performance text searching, consider implementing full-text search capabilities or dedicated search engines like Elasticsearch. When hosting database applications, ensure your infrastructure can handle the computational demands of pattern matching operations. Modern VPS services provide the flexibility to scale database resources as your pattern matching requirements grow, while dedicated servers offer the performance consistency needed for intensive database operations.

Additional resources for mastering SQL pattern matching include the official documentation for MySQL pattern matching and PostgreSQL pattern matching functions, which provide comprehensive references for advanced implementations.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.