Home / SQL / Wildcards

Wildcards

Overview

Wildcards are special characters used with the LIKE operator to match patterns in text. They allow flexible pattern matching when you don't know the exact value.

Wildcards are essential for search functionality, allowing users to find data with partial information.

SQL Wildcards

SQL supports several wildcard characters for pattern matching:

Wildcard	Description	Example
`%`	Matches zero or more characters	`'a%'` matches 'a', 'ab', 'abc', 'abcd'
`_`	Matches exactly one character	`'a_c'` matches 'abc', 'a1c', 'axc'
`[charlist]`	Matches any single character in brackets (SQL Server, Access)	`'[abc]'` matches 'a', 'b', or 'c'
`[^charlist]`	Matches any single character NOT in brackets (SQL Server, Access)	`'[^abc]'` matches any character except 'a', 'b', 'c'

The % Wildcard

The % wildcard represents zero, one, or multiple characters.

Examples with %

Starts with 'John'

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

Matches: 'John', 'Johnny', 'Johnson', 'John Doe'

Ends with 'com'

SELECT * FROM Customers
WHERE email LIKE '%com';

Matches: 'example.com', 'test.com', 'com'

Contains 'test'

SELECT * FROM Customers
WHERE email LIKE '%test%';

Matches: 'test@example.com', 'example@test.com', 'test123@example.com'

Starts and ends with specific patterns

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

Matches: 'ProMax', 'Professional Max', 'Pro Ultra Max'

The _ Wildcard

The _ wildcard represents exactly one character.

Examples with _

Four characters starting with 'J'

SELECT * FROM Customers
WHERE name LIKE 'J___';

Matches: 'John', 'Jane', 'Joan' (if exactly 4 characters)

Pattern with specific position

SELECT * FROM Products
WHERE product_code LIKE 'ABC_123';

Matches: 'ABC1123', 'ABCX123', 'ABC0123' (one character between ABC and 123)

Multiple underscores

SELECT * FROM Customers
WHERE phone LIKE '555-____';

Matches: '555-1234', '555-5678' (exactly 4 digits after 555-)

Combining Wildcards

You can combine % and _ wildcards for complex patterns:

Complex pattern matching

SELECT * FROM Products
WHERE product_name LIKE 'Laptop_Pro%';

Matches: 'Laptop1Pro', 'LaptopXPro Max', 'LaptopAProfessional'

Multiple patterns

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

Matches: Email addresses with at least one character before @ and ending in .com

Bracket Wildcards [charlist]

Note: Bracket notation is specific to SQL Server and Access. PostgreSQL uses different syntax, and MySQL doesn't support it natively.

Match specific characters (SQL Server)

SELECT * FROM Customers
WHERE name LIKE '[JM]ohn';

Matches: 'John' or 'Mohn'

Range of characters

SELECT * FROM Products
WHERE product_code LIKE '[A-C]%';

Matches: Product codes starting with 'A', 'B', or 'C'

Exclude characters

SELECT * FROM Customers
WHERE name LIKE '[^J]ohn';

Matches: Names like 'Mohn', 'Sohn', but NOT 'John'

Escaping Wildcards

To search for literal wildcard characters, escape them:

Escape wildcards

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

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

-- SQL Server alternative using brackets
SELECT * FROM Products
WHERE discount LIKE '%[%]%';
SELECT * FROM Products
WHERE code LIKE '%[_]%';

Performance Considerations

Leading wildcards are slow: LIKE '%pattern' requires full table scan
Trailing wildcards can use indexes: LIKE 'pattern%' may use indexes
Underscore wildcards: May prevent index usage depending on position
Multiple wildcards: More wildcards = slower queries

Performance Comparison

Fast (may use index):
  LIKE 'John%'
  LIKE 'ABC_123'

Slower (full table scan):
  LIKE '%John'
  LIKE '%John%'
  LIKE '_ohn%'

Wildcard Patterns Summary

Pattern	Description	Example Matches
`'a%'`	Starts with 'a'	'a', 'ab', 'abc', 'apple'
`'%a'`	Ends with 'a'	'a', 'ba', 'ca', 'banana'
`'%a%'`	Contains 'a'	'a', 'ab', 'ba', 'cat'
`'a_c'`	Three characters, starts with 'a', ends with 'c'	'abc', 'a1c', 'axc'
`'a__c'`	Four characters, starts with 'a', ends with 'c'	'ab1c', 'a12c', 'axxc'

Best Practices

Use specific patterns: More specific = faster queries
Avoid leading wildcards: When possible, use trailing wildcards
Escape user input: Prevent wildcard injection in applications
Consider full-text search: For complex searches, use dedicated search features
Test performance: Profile queries with wildcards on large datasets

Common Use Cases

Search functionality in applications
Finding partial matches in text fields
Filtering by product codes or IDs
Email domain filtering
Pattern-based data extraction

Next Steps

Learn about using LIKE with wildcards in the LIKE tutorial, or explore other filtering options like IN and BETWEEN.

← Like In →