SQL Wildcards

Welcome to The Coding College, where we simplify coding concepts to help you grow as a developer! In this tutorial, we’ll dive into SQL Wildcards, a powerful feature that makes database searches flexible and efficient.

What Are SQL Wildcards?

SQL Wildcards are special characters used with the LIKE operator to search for patterns in a database. They allow you to find data that matches incomplete or fuzzy criteria, making them essential for flexible data retrieval.

Commonly Used Wildcards

WildcardDescriptionExample
%Represents zero, one, or multiple characters.'A%' matches “Apple,” “Andrew,” etc.
_Represents a single character.'A_' matches “An,” “Ax,” etc.
[ ]Matches any character within the brackets (SQL Server).'H[ae]llo' matches “Hello” or “Hallo.”
[^ ]Matches any character NOT in the brackets (SQL Server).'H[^ae]llo' excludes “Hello” and “Hallo.”
-Represents a range of characters within brackets (SQL Server).'H[a-c]llo' matches “Hallo,” “Hbllo,” or “Hcllo.”

Syntax

SELECT column_name  
FROM table_name  
WHERE column_name LIKE pattern;

Example Table: products

product_idproduct_namecategoryprice
1Apple iPhoneElectronics999
2Samsung GalaxyElectronics799
3Dell InspironComputers549
4Apple WatchWearables399
5HP PavilionComputers649

Wildcard Examples

1. Using % to Find Products Starting with “Apple”

SELECT *  
FROM products  
WHERE product_name LIKE 'Apple%';

Result:

product_idproduct_namecategoryprice
1Apple iPhoneElectronics999
4Apple WatchWearables399

2. Using _ to Find 5-Character Product Names

SELECT *  
FROM products  
WHERE product_name LIKE '_____';

Result:
No matches (since all product names are longer than 5 characters).

3. Using [ ] to Find Product Names Containing “HP” or “Apple”

SELECT *  
FROM products  
WHERE product_name LIKE '[HA]P%';

Result:

product_idproduct_namecategoryprice
1Apple iPhoneElectronics999
5HP PavilionComputers649

4. Using [^ ] to Exclude Product Names Starting with “Apple”

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

Result:

product_idproduct_namecategoryprice
2Samsung GalaxyElectronics799
3Dell InspironComputers549
5HP PavilionComputers649

5. Using - for Ranges

SELECT *  
FROM products  
WHERE product_name LIKE 'D[a-l]%';

Result:

product_idproduct_namecategoryprice
3Dell InspironComputers549

Combining Wildcards with AND/OR

Example: Finding Products That Start with “Apple” or Contain “Galaxy”

SELECT *  
FROM products  
WHERE product_name LIKE 'Apple%' OR product_name LIKE '%Galaxy%';

Result:

product_idproduct_namecategoryprice
1Apple iPhoneElectronics999
2Samsung GalaxyElectronics799
4Apple WatchWearables399

Real-World Applications

  • Search Filters:
    • Enable flexible product or user search with partial names.
SELECT * FROM customers WHERE name LIKE '%John%';
  • Data Cleaning:
    • Identify invalid or incomplete records.
SELECT * FROM users WHERE email NOT LIKE '%@%.%';
  • Categorical Data Analysis:
    • Filter rows matching specific categories or subcategories.
SELECT * FROM products WHERE category LIKE 'Electr%';
  • Audit Logs:
    • Find entries matching specific patterns in logs or activity records.
SELECT * FROM logs WHERE activity LIKE '%failed%';

Best Practices

  • Optimize Wildcard Placement:
    • Avoid starting patterns with %, as it can slow down queries.
SELECT * FROM table WHERE column LIKE '%value%'; -- Slower  
SELECT * FROM table WHERE column LIKE 'value%'; -- Faster
  • Use Indexes:
    • Ensure the column being searched is indexed for better performance.
  • Be Specific:
    • Use targeted wildcards to minimize unnecessary matches and improve speed.
  • Test Queries:
    • Validate results with smaller datasets before running on production.

Conclusion

SQL Wildcards provide a dynamic way to search and filter data in your database. By mastering these techniques, you can enhance query flexibility and make your data searches more efficient.

For more SQL tutorials and practical coding guides, visit The Coding College and take your database skills to the next level!

Leave a Comment