SQL LIKE Operator

The SQL LIKE operator is used for pattern matching within a WHERE clause, allowing you to filter records based on a specified pattern.

The LIKE operator is typically used with wildcard characters % and _ to match a sequence of characters or a single character, respectively.

In this tutorial, we will go through the SQL LIKE operator, its syntax, and how to use this clause in filtering operations in SQL statements, with well detailed examples.

Syntax of SQL LIKE Operator

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

</>
Copy
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Each part of this syntax has a specific purpose:

  • SELECT: Specifies the columns to retrieve from the table.
  • FROM: Specifies the table from which to retrieve data.
  • WHERE: Introduces the condition used to filter the data.
  • LIKE: Checks if the specified pattern matches a column’s value.
  • pattern: A sequence that uses wildcards to specify matching criteria.

The % wildcard represents zero or more characters, while _ represents a single character.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the LIKE operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a table named students:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

Examples: Using LIKE Operator in Queries

Now, let’s explore different scenarios of using the LIKE operator with this table.

SQL LIKE Operator -

1. Using LIKE with % for Matching Patterns

To select students whose names contain the letter “a”:

</>
Copy
SELECT * FROM students
WHERE name LIKE '%a%';

This query returns rows where the name includes “a” anywhere within it, displaying records for Alice, Charlie, David, and Eva.

SQL LIKE Operator - Using LIKE with % for Matching Patterns

2. Using LIKE to Match Starting Characters

To select students whose names start with “A”:

</>
Copy
SELECT * FROM students
WHERE name LIKE 'A%';

This query returns rows where the name starts with “A”, displaying only the record for Alice.

SQL LIKE Operator - Using LIKE to Match Starting Characters

3. Using LIKE to Match Ending Characters

To select students whose names end with “e”:

</>
Copy
SELECT * FROM students
WHERE name LIKE '%e';

This query returns rows where the name ends with “e”, displaying records for Alice and Charlie.

SQL LIKE Operator - Using LIKE to Match Ending Characters

4. Using LIKE with _ for Single Character Matching

To select students whose names have “a” as the second character:

</>
Copy
SELECT * FROM students
WHERE name LIKE '_a%';

This query returns rows where the second character is “a”, displaying records for Alice and David.

SQL LIKE Operator - Using LIKE with _ for Single Character Matching

5. Combining LIKE with NOT for Exclusions

To select students whose names do not contain the letter “a”:

</>
Copy
SELECT * FROM students
WHERE name NOT LIKE '%a%';

This query returns students whose names do not contain the letter “a”, displaying only the record for Bob.

We have used SQL NOT Operator.

SQL LIKE Operator - Combining LIKE with NOT for Exclusions

FAQs for SQL LIKE

1. What does the SQL LIKE operator do?

The LIKE operator is used for pattern matching in SQL queries. It allows you to search for specific patterns within text fields using wildcards like % and _.

2. How does the % wildcard work with LIKE?

The % wildcard represents zero or more characters in a pattern. For example, 'A%' matches any string that starts with “A”.

3. What does the _ wildcard do in LIKE?

The _ wildcard represents a single character. For example, '_b%' matches any string with “b” as the second character.

4. Can I use LIKE with other SQL operators?

Yes, LIKE can be combined with NOT to exclude patterns or with AND and OR to create complex conditions.

5. Is LIKE case-sensitive in SQL?

Case sensitivity in LIKE depends on the database. In MySQL, it is case-insensitive by default, but you can change collation settings to make it case-sensitive.