SQL Wildcards

---

When you need to perform searches in SQL but don’t know the exact value you’re looking for, wildcards can be your best friend. SQL wildcards are special characters used with the LIKE operator to search for patterns in a column. Instead of matching exact strings, wildcards let you find data that meets a specific pattern, which is especially useful when working with text data.

---

1. What are SQL Wildcards?

SQL wildcards are special characters used in combination with the LIKE operator to search for data in a flexible way. These wildcards allow you to match patterns rather than specific values. The most commonly used wildcards are:

• Percent sign (%): Matches zero or more characters.
• Underscore (_): Matches exactly one character.
• Square brackets ([]) (for specific database systems like SQL Server): Matches any single character within the brackets.

These wildcards make it easy to search for incomplete or unknown information.

---

2. Wildcard % (Percent Sign)

The percent sign (%) is used to match zero or more characters in a pattern. This wildcard is one of the most powerful tools when searching for strings with uncertain prefixes or suffixes.

Example:

Suppose you want to find all employees whose names start with "A":

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'A%';

Explanation:

• The % matches any sequence of characters (including no characters at all) after the A.
• This query will return all employees whose first name starts with the letter "A" (e.g., "Alice", "Andrew", "Adam").

Result:

first_name	last_name
Alice	Johnson
Andrew	Smith
Adam	Brown

---

3. Wildcard _ (Underscore)

The underscore (_) is used to match exactly one character in a pattern. This wildcard is useful when you know the length of the string but are unsure about certain characters.

Example:

To find employees whose first name is exactly four characters long and starts with the letter "A":

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'A___';

Explanation:

• The A___ pattern matches any name starting with A followed by exactly three other characters.
• This will match names like "Abe", "Ava", but not "Alice".

Result:

first_name	last_name
Abe	Williams
Ava	Davis

---

4. Combining % and _ Wildcards

You can combine the % and _ wildcards to create more complex patterns that match a specific number of characters in some places and any number of characters in others.

Example:

If you want to find all employees whose first name starts with "A", followed by two characters, and ends with "e":

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'A__e';

Explanation:

• The A__e pattern matches any name that starts with A, followed by exactly two characters, and ends with e. For instance, it would match "Ace", "Abe", but not "Alice".

Result:

first_name	last_name
Abe	Williams
Ace	Thomas

---

5. Using Wildcards with SQL NOT LIKE

The NOT LIKE operator can also be used with wildcards to exclude patterns from your search results. It allows you to filter out records that match a specific pattern.

Example:

If you want to find employees whose names do not start with "A":

SELECT first_name, last_name
FROM employees
WHERE first_name NOT LIKE 'A%';

Explanation:

• The NOT LIKE 'A%' pattern excludes all names that start with "A", returning only names that don’t begin with "A".

Result:

first_name	last_name
Bob	Smith
Charles	Davis
Emma	Roberts

---

6. Using Square Brackets ([]) for Character Ranges

In some SQL databases like SQL Server, you can use square brackets ([]) to define a range or list of characters that a single character in the pattern can match. This is useful when you want to restrict the matches to a certain set of characters.

Example:

To find all employees whose first name contains "A" or "E" as the second character:

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE '_[AE]%';

Explanation:

• The _ matches any single character in the first position, while [AE] restricts the second character to either A or E.
• This query will match names like "Alice", "Eve", but not "Bob" or "Charles".

Result:

first_name	last_name
Alice	Johnson
Eve	Davis

---

7. Using Negation in Square Brackets

You can also use a caret (^) inside square brackets to negate a set of characters. This is useful when you want to exclude certain characters from matching.

Example:

If you want to find all employees whose first name starts with any character except A, B, or C:

SELECT first_name, last_name
FROM employees
WHERE first_name LIKE '[^ABC]%';

Explanation:

• The [^ABC] pattern matches any character except A, B, or C at the start of the first name.
• This query will match names starting with letters like "D", "E", "F", etc.

Result:

first_name	last_name
Emma	Roberts
George	Taylor

---

8. Wildcards in Numeric Values

You can also use SQL wildcards with numeric data, though this is less common. For example, in a product_codes column, you might use a wildcard to search for all product codes that start with "A" followed by any digits.

Example:

To find all product codes that begin with the letter "A" and are followed by any digits:

SELECT product_code, product_name
FROM products
WHERE product_code LIKE 'A%';

Explanation:

• The A% wildcard pattern matches any product code starting with "A" and followed by any combination of characters, which could include digits or letters.

Result:

product_code	product_name
A001	Widget
A2345	Gadget

---

---