SQL LIKE Operator

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

Here’s the basic syntax of the LIKE operator:

There are two wildcards often used with the LIKE operator:

1. % – Represents zero, one, or multiple characters.
2. _ – Represents a single character.

Examples:

Using % Wildcard: Suppose you have a table named employees with a column name. You want to find all employees whose names start with the letter ‘A’.

This will return all names starting with ‘A’.

Using _ Wildcard:If you want to find all employees whose names are exactly five characters long:

Combining Wildcards: You can also combine wildcards:

To find all names that end with ‘son’:

To find all names that have ‘er’ as the second and third characters:

Case Sensitivity:

By default, the LIKE operator is case-insensitive. However, this can depend on the SQL server you are using. To perform a case-sensitive search, you might need to use server-specific functions or configurations.

Tips:

• Always be cautious when using the LIKE operator with wildcards, especially with leading wildcards (% at the beginning of the pattern). Such queries can be resource-intensive as they require a full table scan.
• Some databases provide full-text search capabilities, which can be more efficient for searching large text fields compared to using LIKE.

Remember, the LIKE operator is powerful but can be slow if not used correctly, so always test your queries on a subset of data before running them on large datasets.

---

Example Table: employees

Suppose we have a table named employees with the following data:

id	name
1	John Doe
2	Jane Smith
3	Alice Johnson
4	Bob Carter
5	Mary Taylor

Using % Wildcard

Find all employees whose names start with the letter ‘J’:

Output:

id	name
1	John Doe
2	Jane Smith

Using _ Wildcard

Find all employees whose names are exactly 4 characters long:

Output:

id	name
1	John Doe

Combining Wildcards

Find all employees whose names end with ‘on’:

Output:

id	name
1	John Doe
3	Alice Johnson

Find all employees whose names have ‘e’ as the second letter:

Output:

id	name
2	Jane Smith

Note:

• The LIKE 'J%' pattern matched names that start with ‘J’, like ‘John Doe’ and ‘Jane Smith’.
• The LIKE '____' pattern matched names that have exactly four characters, which in this case was ‘John’.
• The LIKE '%on' pattern matched names that end with ‘on’, like ‘John Doe’ and ‘Alice Johnson’.
• The LIKE '_e%' pattern matched names where ‘e’ is the second letter, which was only ‘Jane Smith’.