LIKE

Overview

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It's used for pattern matching with text data.

LIKE is essential for searching text when you don't know the exact value, allowing partial matches using wildcards (covered in detail in Wildcards tutorial).

Basic Syntax

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

Wildcards Used with LIKE

LIKE uses two main wildcards:

Examples

Example 1: Starts With Pattern

SELECT * FROM Customers
WHERE name LIKE 'John%';

This matches: 'John', 'Johnny', 'Johnson', 'John Doe', etc.

Example 2: Ends With Pattern

SELECT * FROM Customers
WHERE email LIKE '%@example.com';

Example 3: Contains Pattern

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

This matches: 'Johnson', 'Jackson', 'Anderson', 'Wilson', etc.

Example 4: Single Character Wildcard

SELECT * FROM Customers
WHERE name LIKE 'J_hn';

This matches: 'John', 'Jean' (if name is 4 characters), but not 'Johnson' or 'Joan'.

Example 5: Fixed Length Pattern

SELECT * FROM Customers
WHERE name LIKE 'J____';

This matches: 'John', 'James', 'Joan' (if exactly 5 characters).

Example 6: Combining Patterns

SELECT * FROM Products
WHERE product_name LIKE 'Laptop%Pro%';

This matches: 'LaptopPro', 'Laptop Professional', 'LaptopProMax', etc.

NOT LIKE

Use NOT LIKE to exclude rows that match a pattern:

SELECT * FROM Customers
WHERE email NOT LIKE '%@test.com';

LIKE with Multiple Conditions

Combine LIKE with AND/OR:

SELECT * FROM Customers
WHERE (name LIKE 'John%' OR name LIKE 'Jane%')
  AND email LIKE '%@example.com';

Case Sensitivity

LIKE behavior with case sensitivity varies by database:

• MySQL: Case-insensitive by default (depends on collation)
• PostgreSQL: Case-sensitive by default (use ILIKE for case-insensitive)
• SQL Server: Case-insensitive by default (depends on collation)
• Oracle: Case-sensitive by default

-- PostgreSQL: Use ILIKE for case-insensitive
SELECT * FROM Customers
WHERE name ILIKE 'john%';

-- Other databases: Use UPPER or LOWER functions
SELECT * FROM Customers
WHERE UPPER(name) LIKE 'JOHN%';

Escaping Wildcards

To search for literal % or _, escape them:

-- Find values containing '%' (syntax varies by database)
SELECT * FROM Products
WHERE discount LIKE '%\%%' ESCAPE '\';

-- Find values containing '_'
SELECT * FROM Products
WHERE code LIKE '%\_%' ESCAPE '\';

-- Alternative: Use brackets (SQL Server)
SELECT * FROM Products
WHERE discount LIKE '%[%]%';

Performance Considerations

• Leading wildcards are slow: LIKE '%pattern' can't use indexes
• Use indexes when possible: LIKE 'pattern%' can use indexes
• Avoid excessive wildcards: Too many wildcards slow queries
• Consider full-text search: For complex searches, use full-text indexing

LIKE vs =

Use = for exact matches, LIKE for pattern matching:

-- Exact match (faster, can use index)
SELECT * FROM Customers
WHERE name = 'John Doe';

-- Pattern match (slower, may not use index)
SELECT * FROM Customers
WHERE name LIKE 'John%';

Common Use Cases

• Searching customer names
• Finding email addresses by domain
• Filtering product codes
• Searching partial matches
• Implementing search functionality

Best Practices

• Use specific patterns: More specific patterns are faster
• Avoid leading wildcards: %pattern is slow
• Consider full-text search: For complex text searches
• Escape user input: Prevent SQL injection in applications
• Test case sensitivity: Understand your database's behavior

Next Steps

Learn more about wildcards in the Wildcards tutorial, or explore other filtering options like IN.