LIKE Operator in SQL

When it comes to searching dynamic databases, you can use the SQL LIKE clause to include patterns. This handy tool is really essential for dynamic pattern matching. Data plays a vital role in countless SQL statements, whether it’s about sifting through text to fetch any specific information or filtering system data in real-time. However, when LIKE isn’t used correctly, it can lead to a variety of performance issues.

In this tutorial, we’ll explore the LIKE clause together, and let’s share all the wonderful insights we can discover together. We’ll also discover some great ways to optimize it, making sure your queries run smoothly and efficiently.

The LIKE clause in SQL is an operator that you can use within a WHERE statement to find specific patterns in a column. It makes searching for data so much easier, and it also supports two primary wildcard characters:

- Percent Sign (%): This is a versatile symbol that can stand for zero, one, or even multiple characters.
- Underscore (_): This symbol represents a single character.

Let’s see the basic syntax of LIKE Clause:

SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;

In the above syntax, column1 and column2 tell us about the column that you want to fetch from the table. Table_name is the table from where you want to fetch the data. The WHERE clause applies the condition where columN must follow the specific pattern provided.

LIKE Operator Implementation

First of all, let’s create a sample table so that we can perform the operation on it.

Creating Sample Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Email VARCHAR(100)
);

Let’s insert some values.

Inserting Values

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Email)
VALUES
(1, 'Alice', 'Johnson', 'HR', '[email protected]'),
(2, 'Bob', 'Smith', 'IT', '[email protected]'),
(3, 'Charlie', 'Williams', 'Finance', '[email protected]'),
(4, 'David', 'Brown', 'IT', '[email protected]'),
(5, 'Eve', 'Davis', 'Marketing', '[email protected]');

Examples of using LIKE clause

1. Finding Names Starting with a Specific Letter

If you’re looking for employees with first names that begin with the letter ‘A’

SELECT * FROM Employees
WHERE FirstName LIKE 'A%';

Output:

2. Finding Names Ending with a Specific Letter

You can find employees who have last names that end with the letter ‘n’.

SELECT * FROM Employees
WHERE LastName LIKE '%n';

Output:

3. Finding Names Containing a Specific Sequence

If you’re looking for employees with first names that include the sequence ‘ar’

SELECT * FROM Employees
WHERE FirstName LIKE '%ar%';

Output:

4. Finding Names with a Specific Character at a Certain Position

To discover employees whose first names have an ‘a’ as the second letter character

SELECT * FROM Employees
WHERE FirstName LIKE '_a%';

Output:

5. Combining Wildcards

To find employees with first names starting with ‘D’ and having three or more characters.

SELECT * FROM Employees
WHERE FirstName LIKE 'D__%';

Output:

Advanced Usage of the LIKE Clause

1. Using LIKE with AND Operator

If you’re looking to find team members in the IT department with email addresses that include ‘David.

SELECT * FROM Employees
WHERE Department = 'IT' AND Email LIKE '%david%';

Output:

2. Using NOT LIKE for Exclusion

To find employees whose first names do not begin with ‘A’

SELECT * FROM Employees
WHERE FirstName NOT LIKE 'A%';

Output:

Performance Issues with LIKE Queries

While the LIKE clause can be quite convenient, it may sometimes lead to slower searches, especially when working with large datasets. Here are a few issues to keep in mind.

1. Using a Wildcard at the Start (%search_term%)

Starting with a % at the beginning of your phrase can narrow the search a bit, as the database will have to go through each row, which might take a little while.

Slower Approach

SELECT * FROM Employees WHERE FirstName LIKE '%vid%';

The table above shows all the rows that are present in it. This happens because the database doesn’t have a way to use an index that could help with the search.

Better Approach

To enhance your search experience, try to avoid starting your search term with a percent sign (%). Instead, consider placing the letters at the beginning of the names for more effective results!

SELECT * FROM Employees WHERE FirstName LIKE 'D%';

Now, the database will utilize indexes to get the result which is much faster.

2. Case Differences in Database Searches

- Some databases treat LIKE queries in a case-insensitive way, which means that words like “david” and “David” are considered the same.
- Some databases like PostgreSQL use case-sensitive LIKE queries.
- If your database is case-sensitive and you’d like to perform a universal search, you can use ILIKE for PostgreSQL.
- Alternatively, you can easily convert your text to lowercase using the LOWER function.

SELECT * FROM Employees WHERE LOWER(FirstName) LIKE 'david';

3. Wildcard Indexing Problems

The indexes work wonderfully with values that are at the start of the string. For instance, you can easily use LIKE ‘Dav%’ to get efficient results. Using a wildcard such as % at the beginning of a string hinders the database’s efficient utilization of the index.

Example of a Slow Query

SELECT * FROM Employees WHERE Email LIKE '%example.com';

- This query is inefficient because the database must check each email to determine if it ends with example.com.
- If you want to try to avoid using wildcards at the beginning of the string. If you need a more advanced search, you might want to consider using Full-Text Search instead.

Best Practices for Using the LIKE Clause

- Avoid Starting Searches with Wildcards: Beginning your search terms with a wildcard like “%” can slow things down a bit. This means the database won’t be able to make the most of its indexing capabilities.
- Utilize FULLTEXT Indexing for Large Searches: With FULLTEXT indexing, searching through long text fields, such as descriptions or emails, becomes a breeze compared to using LIKE.
- Use IN() Instead of LIKE: It is less efficient to write multiple LIKE clauses than to use IN() when searching for multiple values.
- Ensure Proper Indexing: Creating an index can really boost the speed of your searches, especially for columns that you frequently filter using LIKE. It’s a great idea to ensure proper indexing is done.
- Use ILIKE for Case-Insensitive Searches: In databases such as PostgreSQL, using LIKE will match strings with uppercase letters, while ILIKE provides a case-insensitive match. This means you can use ILIKE when you want to find matches regardless of lettercase.
- Use REGEXP for complex patterns: Unlike LIKE, Regular Expressions (REGEXP) allow you to explore advanced pattern-matching techniques that make your search even more effective!

Conclusion

The SQL LIKE clause is a wonderful tool for discovering specific patterns in text, as it gives you the freedom to perform countless searches, whether you’re looking for names that start with a particular letter or sifting through emails from certain domains. Just a gentle reminder that using LIKE statements can occasionally slow down database processing, so it’s beneficial to keep that in mind. Being careful with them can really help you achieve optimal performance when working with queries in the database!

Improve performance by following best practices: skip leading wildcards, use FULLTEXT instead of LIKE for large text, consider regular and non-cluster indexes, and utilize ILIKE with regular expressions. Master SQL with Intellipaat’s Microsoft SQL Server Certification Training and boost your database skills!