SQL LIKE Operator

What is SQL LIKE?

The LIKE operator in SQL is used to search for a specified pattern in a column. It is typically used with the WHERE clause to filter records based on patterns in string data. Two common wildcards are used with the LIKE operator:

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Syntax of SQL LIKE

The basic syntax for the LIKE operator is:

SELECT column1, column2
FROM table_name
WHERE column_name LIKE pattern;

Here, pattern refers to the string pattern you are searching for in a particular column.

Using SQL LIKE with %

The % wildcard can be used to find records where a particular string appears anywhere within a column:

Example: Find employees whose name starts with 'A':

SELECT Name
FROM Employees
WHERE Name LIKE 'A%';

This query will return names like "Arshiya", "Anam", or "Akef".

Using SQL LIKE with _

The _ wildcard is used to find records where a single character can vary:

Example: Find employees whose name has exactly 4 characters, and starts with '':

SELECT Name
FROM Employees
WHERE Name LIKE 'A___';

This query will return names like "Akef" or "Anam" but not "Arshiya" (which has 5 characters).

Combining LIKE with AND or OR

You can combine multiple LIKE conditions using logical operators such as AND or OR:

Example: Find employees whose name starts with 'A' and ends with 'M':

SELECT Name
FROM Employees
WHERE Name LIKE 'A%' AND Name LIKE 'M%';

This query will return names like "Anam".

Case Sensitivity in SQL LIKE

Whether LIKE is case-sensitive depends on the database system. For example, MySQL is not case-sensitive by default, while PostgreSQL is.

To perform a case-insensitive search in a case-sensitive database, you can use LOWER() or UPPER() functions:

SELECT Name
FROM Employees
WHERE LOWER(Name) LIKE 'tausif%';

LIKE in Popular Databases

The LIKE operator is supported by most relational database systems:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database
• SQLite