SQL LIKE and NOT LIKE Operators

---

When working with SQL queries, there are times when you need to search for data that matches a certain pattern, but without knowing the exact values. The LIKE operator allows you to perform pattern matching in SQL, enabling you to search for records that contain specific sequences of characters.

On the flip side, the NOT LIKE operator is used to exclude records that match a certain pattern. These operators are essential when you're dealing with textual data (like names, addresses, or descriptions) and want to find or exclude certain patterns.

---

1. Basic Syntax of the SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

• column_name: The name of the column where you want to search for the pattern.
• pattern: The pattern you want to match. You can use wildcards to define the pattern.

Wildcards in SQL LIKE

SQL uses two primary wildcards with the LIKE operator:

• Percent sign (%): Represents zero or more characters.
• Underscore (_): Represents a single character.

Example:

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

Explanation:

• The query will return all employees whose first name starts with the letter A. The % wildcard allows any characters to follow A.

Result:

first_name	last_name
Alice	Johnson
Andrew	Smith
Adam	Brown

---

2. Using the Percent Sign (%) Wildcard

The percent sign (%) is used to represent zero, one, or more characters. This allows for broad pattern matching.

Example:

To find all employees whose last name ends with "son":

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

Explanation:

• The %son pattern matches any last name that ends with "son", such as "Johnson" or "Wilson".

Result:

first_name	last_name
Alice	Johnson
James	Wilson

---

3. Using the Underscore (_) Wildcard

The underscore (_) represents exactly one character. It’s used when you need to match a specific number of characters.

Example:

If you want to find all employees whose first name is exactly 5 characters long and starts with the letter A:

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

Explanation:

• The A____ pattern will match any first name that starts with A and is followed by exactly four other characters.

Result:

first_name	last_name
Alice	Johnson
Angie	Thomas

---

4. Combining % and _ Wildcards

You can combine the % and _ wildcards to create more complex patterns.

Example:

To find employees whose first name starts with A, followed by exactly two characters, and ends with e:

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

Explanation:

• The A__e pattern will match names like "Abe", "Ace", and "Aye" (but not "Alice").

Result:

first_name	last_name
Abe	Williams
Ace	Daniels

---

5. SQL NOT LIKE Operator

The NOT LIKE operator is used to exclude rows that match a specified pattern. It’s useful when you want to retrieve all records except those that match a given pattern.

Example:

Suppose you want to find employees whose first name does not start with the letter A:

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

Explanation:

• The NOT LIKE 'A%' pattern will exclude any employee whose first name starts with the letter A.

Result:

first_name	last_name
Bob	Smith
Charles	Davis
Emma	Roberts

---

6. Case Sensitivity with LIKE and NOT LIKE

In some SQL databases, the LIKE and NOT LIKE operators are case-insensitive by default (for example, MySQL), while in others (such as PostgreSQL), they are case-sensitive. To make the pattern matching case-insensitive, you can use the LOWER() or UPPER() functions in combination with LIKE or NOT LIKE.

Example (PostgreSQL or case-sensitive environments):

To find employees whose first name starts with a or A (case-insensitive):

SELECT first_name, last_name
FROM employees
WHERE LOWER(first_name) LIKE 'a%';

Explanation:

• The LOWER(first_name) function ensures that the comparison is case-insensitive, so it will match names starting with both a and A.

Result:

first_name	last_name
Alice	Johnson
Andrew	Smith

---

7. Using LIKE with Numbers and Special Characters

The LIKE operator is not limited to text columns—it can also be used to match patterns in columns containing numbers or special characters. You can use the same wildcards (% and _) to match numeric or alphanumeric patterns.

Example:

If you have a products table and want to find product codes that start with "A" and are followed by any characters:

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

Explanation:

• This query will match any product code that starts with the letter A, such as "A001", "A2345", etc.

Result:

product_code	product_name
A001	Widget
A2345	Gadget

---

8. Using NOT LIKE with Special Characters

You can also use the NOT LIKE operator to exclude patterns with numbers or special characters.

Example:

If you want to exclude product codes that start with A:

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

Explanation:

• This query will exclude all products whose product codes start with the letter A.

Result:

product_code	product_name
B123	Tool
C5678	Device

---

---