SQL LIKE Operator

SQL LIKE Operator

The SQL LIKE operator is used to search for a specified pattern in a column. This command is essential for performing flexible and partial matching in queries, allowing for more dynamic and precise data retrieval.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
  • SELECT column_name(s): Specifies the columns to retrieve.
  • FROM table_name: Specifies the table to query data from.
  • WHERE column_name LIKE pattern: Filters the result set to include only rows where the column value matches the specified pattern.
  • pattern: Specifies the pattern to search for. Wildcards used in the pattern include % (matches any sequence of characters) and _ (matches any single character).

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the LIKE operator to perform a query.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

Here, we define the employees table with columns for employee_id, first_name, last_name, and email. The employee_id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email) VALUES ('John', 'Doe', 'john.doe@example.com');
INSERT INTO employees (first_name, last_name, email) VALUES ('Jane', 'Smith', 'jane.smith@example.com');
INSERT INTO employees (first_name, last_name, email) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com');
INSERT INTO employees (first_name, last_name, email) VALUES ('Bob', 'Brown', 'bob.brown@example.com');
INSERT INTO employees (first_name, last_name, email) VALUES ('Charlie', 'Davis', 'charlie.davis@example.com');

Here, we insert five rows of data into the employees table.

Step 4: Using the LIKE Operator

This step involves using the LIKE operator to find employees whose last name starts with 'J'.

SELECT employee_id, first_name, last_name, email
FROM employees
WHERE last_name LIKE 'J%';

This command retrieves the employee_id, first_name, last_name, and email of employees whose last name starts with 'J'. The % wildcard matches any sequence of characters following 'J'.

In this example, the query will return the rows for 'Alice Johnson' as her last name starts with 'J'.

Step 5: Using the LIKE Operator with Different Patterns

This step involves using the LIKE operator with different patterns to find employees whose email addresses contain 'example'.

SELECT employee_id, first_name, last_name, email
FROM employees
WHERE email LIKE '%example%';

This command retrieves the employee_id, first_name, last_name, and email of employees whose email addresses contain 'example'. The % wildcard matches any sequence of characters before and after 'example'.

In this example, the query will return all rows because all email addresses contain 'example'.