Like operator in SQL

The LIKE operator in SQL is used to filter data based on a specified pattern. The pattern can be a string of characters that contain wildcards.
The LIKE operator returns all rows where the selected pattern matches a portion of the data in the column.
In this article, we will discuss the LIKE operator in SQL, its syntax, parameters, and examples of how to use it in SQL Server.

Description:

The LIKE operator is used to compare a string to a pattern. The pattern can contain wildcards that match any character or number of characters. The most commonly used wildcards are the percent sign (%) and the underscore (_).

The SQL LIKE condition enables pattern matching in a query using wildcards. We can use it in the WHERE clause of SELECT, INSERT, UPDATE, or DELETE statements.

Syntax:

The basic syntax of the LIKE operator in SQL is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE 'pattern';

The pattern can contain one or more wildcard characters.

The following table shows some common patterns used with the LIKE operator:

In the first example, the % character matches any string that starts with 'Jo'. The second example uses the _ character to match any string that begins with ‘J’, has two additional characters, and ends with ‘n’.

The third example uses [charlist] to match any string that starts with ‘J’ or ‘j’, followed by ‘o’, ‘h’, and ‘n’. The fourth example uses [^charlist] to match any string that does not start with 'J' or 'j', followed by ‘o’, ‘h’, and ‘n’.

The following is the Employees table that you can use to practice the LIKE operator:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20)
);

INSERT INTO employees (employee_id, first_name, last_name, email, phone)
VALUES
    (1, 'John', 'Doe', 'johndoe@abc.com', '555-1234'),
    (2, 'David', 'Smith', 'davidsmith@xyz.com', '786-2345'),
    (3, 'Shekh', 'Ali', 'shekhali@xyz.com', '786-3456'),
    (4, 'Jill', 'Johnson', 'jilljohnson@example.com', '555-4567'),
    (5, 'Jack', 'Brown', 'jackbrown@example.com', '555-5678');

Here are a few examples of how to use the LIKE operator with the Employees table:

Example 1: Using % Wildcard in the LIKE Condition

SELECT * FROM employees
WHERE first_name LIKE 'J%';

This statement returns all employees whose first name starts with J.

Example 2: Using Multiple % Wildcards in the LIKE Condition

SELECT * FROM employees
WHERE email LIKE '%xyz%';

This statement returns all employees whose email contains the word “xyz”.

Example 3: Using _ Wildcard in the LIKE Condition

SELECT * FROM employees
WHERE phone LIKE '555-____';

This statement returns all employees whose phone numbers start with “555-” and have four additional digits.

Example 4: Using Multiple _ Wildcards in the LIKE Condition

SELECT * FROM employees
WHERE phone LIKE '786-___%';

This statement returns all employees whose phone numbers start with “786-” and have at least three additional digits.

Example 5: Using the NOT Operator with the LIKE Condition

SELECT * FROM employees
WHERE last_name NOT LIKE 'D%';

This statement returns all employees whose last name does not start with D.

Example 6: The [list of characters] wildcard

SELECT * FROM employees
WHERE last_name LIKE '[AB]%';

In a query, square brackets enclosing a list of characters (e.g., [ABC]) indicate that a single character in the search term must match one of the characters in the list. For instance, a query may return Employees whose last names start with either A or B by using the search term [AB]% in the appropriate column.

Example 7: The [character-character] wildcard

Using square brackets with a character range, such as [A-D], denotes a particular character that should fall within a designated range. For example, the following query retrieves employee records whose first character in the last name is the letter between the range A to D.

SELECT * FROM employees
WHERE last_name LIKE '[A-D]%';

Example 8: The [^Character List or Range] wildcard

Suppose we want to find all employees whose last name starts with something other than the letters 'A' or 'B'. We can use the [ ^ ] wildcard character to achieve this.
The following SQL query will return all employees whose last name does not start with 'A' or 'B':

SELECT * FROM employees
WHERE last_name LIKE '[^AB]%';

In the above query, the [ ^AB] pattern matches any character that is not an 'A' or 'B'. The % wildcard matches any string of zero or more characters after the first character.
So, the query will return all rows from the Employees table where the last_name column does not start with 'A' or 'B'.

It will include employees whose last names start with any other letter and those whose last names start with a number, symbol, or whitespace character.

Using the ESCAPE keyword with the LIKE operator in SQL Server

Suppose we have a table called products with the following columns and data:

CREATE TABLE products (
  product_id INT,
  product_name VARCHAR(50),
  product_code VARCHAR(10)
);

INSERT INTO products VALUES
(1, 'iPhone', 'APP_123'),
(2, 'Galaxy', 'SAM_456'),
(3, 'Pixel', 'GOO_789'),
(4, 'OnePlus', 'OP_10%'),
(5, 'Xperia', 'SON_234');

We want to find all products whose product_code column contains a literal percent sign ('%'). Usually, the percent sign is a wildcard character that matches any string of zero or more characters in a pattern, but we want to find product codes that actually contain the percent sign as part of the code.

We can use the ESCAPE keyword with the LIKE operator to achieve this. In this case, we can use the backslash \ character as the escape character since it is a common choice for escaping special characters in strings.
Here is the SQL query to find all products whose product_code column contains a literal percent sign:

SELECT * FROM products
WHERE product_code LIKE '%\%%' ESCAPE '\';

Output:

In this query, the pattern '%\%%' will match any product_code value that contains a literal percent sign ('%'). The backslash \ is used as the escape character, and the escape character in the pattern precedes the percent sign.
So, the query will return the row with product_id ‘4’ since its product_code value contains a literal percent sign. Note that we have used single quotes to enclose the escape character and the pattern itself.

Properties of the LIKE operator in SQL

The LIKE operator in SQL is used to search for patterns in strings. Here are some properties of the LIKE operator in SQL:

1. LIKE is Case Insensitive: By default, the LIKE operator in SQL is case-insensitive, which means it will match patterns regardless of whether the letters are uppercase or lowercase. For example, the pattern 'john' will match strings like 'John', 'joHn', and so on. However, using the COLLATE keyword with a case-sensitive collation, you can change this behaviour.
2. LIKE supports wildcard characters: The LIKE operator supports two wildcard characters: % and _. The % character matches any string of zero or more characters, while the _ character matches any single character. For example, the pattern ‘J%’ will match strings that start with the letter ‘J’, while the pattern ‘J___’ will match strings that start with the letter ‘J’ and have three additional characters.
3. LIKE can be used with the NOT operator: You can use the NOT operator with the LIKE operator to search for strings that do not match a particular pattern. For example, the pattern NOT LIKE ‘J%’ will match all strings that do not start with the letter ‘J’.
4. LIKE can be used with escape characters: You can use the backslash character \ as an escape character to search for strings that contain wildcard characters. For example, the pattern ‘5\% off’ will match strings that contain the text ‘5% off’.
5. LIKE can be used with any string column: The LIKE operator can be used with any string column in a table, such as a column that stores names, email addresses, or phone numbers.

These are some of the main properties of the LIKE operator in SQL. By understanding these properties, you can use the LIKE operator effectively to search for patterns in strings and retrieve the data you need.

References: MSDN-LIKE (Transact-SQL)

FAQs

The following are some frequently asked questions related to the SQL LIKE operator:

You might want to read this too:

• Having VS Where Clause in SQL
• COALESCE in SQL With Examples and Use Cases for Handling NULL Values
• SQL Server CONVERT Function: How to Convert Data Types in SQL Server
• Delete vs Truncate vs Drop in SQL Server
• SQL Server TRY_CAST() Function – Understanding TRY_CAST() in SQL with [Examples]
• SQL Server Try Catch: Error Handling in SQL Server With [Examples]
• Triggers in SQL Server
• Different ways to delete duplicate rows in SQL Server
• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• UNION vs UNION ALL in SQL SERVER
• View In SQL | Types Of Views In SQL Server
• SQL pivot tables: Understanding pivot tables in SQL Server
• Group by Month in SQL: A Comprehensive Guide with Examples in SQL Server

Let others know about this post by sharing it and leaving your thoughts in the comments section.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024