SQL wildcards are powerful tools that allow you to perform flexible and dynamic searches in your database. They enable you to match patterns rather than exact values, making your SQL queries more versatile. In this article, we will explore the commonly used SQL wildcards, such as ‘%’ and ‘_’, and provide examples of how to use them in queries.
SQL Wildcards:
1. Percentage (%) Wildcard:
The ‘%’ wildcard is used to represent zero or more characters in a string.
- Example 1: Find all names starting with ‘J’:
SELECT * FROM employees WHERE name LIKE 'J%';- Example 2: Find all email addresses ending with ‘@example.com’:
SELECT * FROM users WHERE email LIKE '%@example.com';2. Underscore (_) Wildcard:
The ‘_’ wildcard is used to represent a single character.
- Example 1: Find all names with ‘a’ as the second letter:
SELECT * FROM customers WHERE name LIKE '_a%';- Example 2: Find all two-letter country codes:
SELECT * FROM countries WHERE code LIKE '__';3. Combining Wildcards:
You can combine wildcards for more complex pattern matching.
- Example: Find all usernames starting with ‘john’ and having exactly 6 characters:
SELECT * FROM accounts WHERE username LIKE 'john%___';4. Using NOT LIKE:
The NOT LIKE operator is used to exclude rows that match a specified pattern.
- Example: Find all products not containing ‘X’ in their name:
SELECT * FROM products WHERE product_name NOT LIKE '%X%';Understanding and effectively using SQL wildcards is essential for crafting dynamic and efficient queries. Whether you’re searching for specific patterns, filtering data, or excluding certain values, wildcards provide the flexibility needed for various scenarios. Experiment with these examples and integrate SQL wildcards into your queries to unlock the full potential of your database searches.