Pattern Matching with LIKE & Wildcards0%

Pattern Matching with LIKE & Wildcards

Beginner12 min read•Updated: 2026-09-12

Pattern Matching with LIKE & Wildcards: Text Search Mechanics

Exact equality matches (WHERE username = 'amit') work when you know the complete, precise string. However, real-world user interfaces require partial searching: finding all customers whose last name starts with "Sha", looking up emails ending with "@gmail.com", or searching for products containing the word "Wireless". In SQL, basic pattern matching is accomplished using the LIKE operator and wildcard characters.

1. The Two Fundamental SQL Wildcards

Wildcard Symbol	Meaning	Example Pattern	Matches	Does NOT Match
`%` (Percent)	Matches zero, one, or multiple characters.	`'A%'`	`'A'`, `'Amit'`, `'Ananya'`	`'Rohan'`
`_` (Underscore)	Matches exactly one single character.	`'R_m'`	`'Ram'`, `'Rom'`, `'R-m'`	`'Rohan'`, `'Rm'`

2. Common Pattern Matching Recipes

1-- 1. Starts With: Find all names starting with 'S'
2SELECT full_name FROM customers WHERE full_name LIKE 'S%';
3 
4-- 2. Ends With: Find all corporate emails ending with '@company.com'
5SELECT email FROM employees WHERE email LIKE '%@company.com';
6 
7-- 3. Contains (Substring): Find all products with 'Bluetooth' anywhere in the title
8SELECT product_name FROM products WHERE product_name LIKE '%Bluetooth%';
9 
10-- 4. Exact Length & Position: Find 5-character postal codes starting with '400'
11SELECT postal_code FROM addresses WHERE postal_code LIKE '400__';
12 
13-- 5. Specific Character Position: Second letter is 'a'
14SELECT full_name FROM students WHERE full_name LIKE '_a%';

3. Escaping Wildcard Characters (`ESCAPE`)

What if you need to search for an actual literal percent sign (%) or underscore (_) stored in the database (e.g., finding a discount code named 'SALE_50%')?

In MySQL, the backslash (\) serves as the default escape character:

1-- Search for literal discount string '50%':
2SELECT * FROM promotions WHERE promo_code LIKE '%50\%%';
3 
4-- Search for literal underscore 'user_test':
5SELECT * FROM accounts WHERE username LIKE '%user\_test%';
6 
7-- Custom Escape Character using ESCAPE clause:
8SELECT * FROM promotions WHERE promo_code LIKE '%50!%%' ESCAPE '!';

4. The Performance Reality: Index Utilization with `LIKE`

How does the MySQL query optimizer handle B-Tree indexes when evaluating LIKE queries?

 LIKE 'Sharma%'   =======> CAN USE B-TREE INDEX! (Index Range Scan)
                           Fast because prefix is known.
3 
 LIKE '%Sharma'   =======> CANNOT USE B-TREE INDEX! (Full Table Scan)
                           Slow because index cannot be traversed backwards.
6 
 LIKE '%Sharma%'  =======> CANNOT USE B-TREE INDEX! (Full Table Scan)
                           Slowest! Must scan every single row on disk.

Pro Tip

[!TIP] For large-scale substring searching across millions of text records (e.g., searching full product catalogs or blog articles), standard LIKE '%word%' is too slow. Use MySQL's Full-Text Search (MATCH ... AGAINST) or external search engines like Elasticsearch!

5. Case Sensitivity in `LIKE`

In MySQL, whether LIKE is case-sensitive depends entirely on the collation of the column:

With the default collation (utf8mb4_0900_ai_ci), LIKE 'amit%' matches 'Amit', 'AMIT', and 'amit'.
To force a case-sensitive pattern match, prefix the expression with the BINARY operator:

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

6. Best Practices & Common Pitfalls

Avoid Leading Wildcards on High-Traffic Queries: A query like WHERE email LIKE '%@gmail.com' on a 50-million-row table will bypass your index, scanning every row and pegging CPU utilization at 100%.
Inverting with NOT LIKE: You can invert matches using NOT LIKE (e.g., WHERE email NOT LIKE '%@spam.com').

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which wildcard character matches zero, one, or multiple characters in a SQL LIKE expression?

Question 2

Which wildcard character matches exactly one single character in a SQL LIKE expression?

Question 3

Which of the following LIKE patterns CAN utilize a standard B-Tree index to perform a fast index range scan?

Question 4

How do you search for a product code that contains an actual literal underscore ('PROMO_') in MySQL?

Question 5

What will the pattern LIKE '_r%' match?

Next Lesson

Handling NULL Values (IS NULL)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Membership Filtering with IN Operator	Handling NULL Values (IS NULL)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Handling NULL Values (IS NULL)

Pattern Matching with LIKE & Wildcards

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Pattern Matching with LIKE & Wildcards: Text Search Mechanics

1. The Two Fundamental SQL Wildcards

Wildcard Symbol	Meaning	Example Pattern	Matches	Does NOT Match
`%` (Percent)	Matches zero, one, or multiple characters.	`'A%'`	`'A'`, `'Amit'`, `'Ananya'`	`'Rohan'`
`_` (Underscore)	Matches exactly one single character.	`'R_m'`	`'Ram'`, `'Rom'`, `'R-m'`	`'Rohan'`, `'Rm'`

2. Common Pattern Matching Recipes

1-- 1. Starts With: Find all names starting with 'S'
2SELECT full_name FROM customers WHERE full_name LIKE 'S%';
3 
4-- 2. Ends With: Find all corporate emails ending with '@company.com'
5SELECT email FROM employees WHERE email LIKE '%@company.com';
6 
7-- 3. Contains (Substring): Find all products with 'Bluetooth' anywhere in the title
8SELECT product_name FROM products WHERE product_name LIKE '%Bluetooth%';
9 
10-- 4. Exact Length & Position: Find 5-character postal codes starting with '400'
11SELECT postal_code FROM addresses WHERE postal_code LIKE '400__';
12 
13-- 5. Specific Character Position: Second letter is 'a'
14SELECT full_name FROM students WHERE full_name LIKE '_a%';

3. Escaping Wildcard Characters (`ESCAPE`)

What if you need to search for an actual literal percent sign (%) or underscore (_) stored in the database (e.g., finding a discount code named 'SALE_50%')?

In MySQL, the backslash (\) serves as the default escape character:

1-- Search for literal discount string '50%':
2SELECT * FROM promotions WHERE promo_code LIKE '%50\%%';
3 
4-- Search for literal underscore 'user_test':
5SELECT * FROM accounts WHERE username LIKE '%user\_test%';
6 
7-- Custom Escape Character using ESCAPE clause:
8SELECT * FROM promotions WHERE promo_code LIKE '%50!%%' ESCAPE '!';

4. The Performance Reality: Index Utilization with `LIKE`

How does the MySQL query optimizer handle B-Tree indexes when evaluating LIKE queries?

 LIKE 'Sharma%'   =======> CAN USE B-TREE INDEX! (Index Range Scan)
                           Fast because prefix is known.
3 
 LIKE '%Sharma'   =======> CANNOT USE B-TREE INDEX! (Full Table Scan)
                           Slow because index cannot be traversed backwards.
6 
 LIKE '%Sharma%'  =======> CANNOT USE B-TREE INDEX! (Full Table Scan)
                           Slowest! Must scan every single row on disk.

Pro Tip

5. Case Sensitivity in `LIKE`

In MySQL, whether LIKE is case-sensitive depends entirely on the collation of the column:

With the default collation (utf8mb4_0900_ai_ci), LIKE 'amit%' matches 'Amit', 'AMIT', and 'amit'.
To force a case-sensitive pattern match, prefix the expression with the BINARY operator:

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

6. Best Practices & Common Pitfalls

Avoid Leading Wildcards on High-Traffic Queries: A query like WHERE email LIKE '%@gmail.com' on a 50-million-row table will bypass your index, scanning every row and pegging CPU utilization at 100%.
Inverting with NOT LIKE: You can invert matches using NOT LIKE (e.g., WHERE email NOT LIKE '%@spam.com').

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which wildcard character matches zero, one, or multiple characters in a SQL LIKE expression?

Question 2

Which wildcard character matches exactly one single character in a SQL LIKE expression?

Question 3

Which of the following LIKE patterns CAN utilize a standard B-Tree index to perform a fast index range scan?

Question 4

How do you search for a product code that contains an actual literal underscore ('PROMO_') in MySQL?

Question 5

What will the pattern LIKE '_r%' match?

Next Lesson

Handling NULL Values (IS NULL)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Membership Filtering with IN Operator	Handling NULL Values (IS NULL)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Pattern Matching with LIKE & Wildcards: Text Search Mechanics

1. The Two Fundamental SQL Wildcards

2. Common Pattern Matching Recipes

3. Escaping Wildcard Characters (ESCAPE)

4. The Performance Reality: Index Utilization with LIKE

5. Case Sensitivity in LIKE

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Handling NULL Values (IS NULL)

Related Lessons

Practice Quiz

Loading Page Content...

Pattern Matching with LIKE & Wildcards: Text Search Mechanics

1. The Two Fundamental SQL Wildcards

2. Common Pattern Matching Recipes

3. Escaping Wildcard Characters (ESCAPE)

4. The Performance Reality: Index Utilization with LIKE

5. Case Sensitivity in LIKE

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Handling NULL Values (IS NULL)

Related Lessons

Practice Quiz

3. Escaping Wildcard Characters (`ESCAPE`)

4. The Performance Reality: Index Utilization with `LIKE`

5. Case Sensitivity in `LIKE`

3. Escaping Wildcard Characters (`ESCAPE`)

4. The Performance Reality: Index Utilization with `LIKE`

5. Case Sensitivity in `LIKE`