SQL - LIKE Operator

The LIKE operator is used in the WHERE condition to filter data based on some specific pattern. It can be used with numbers, string, or date values. However, it is recommended to use the string values.

The LIKE operator in MS SQL Server, SQLite, MySQL database are not case-sensitive, whereas it is case-sensitive in Oracle, and PostgreSQL database.

Syntax:

SELECT * FROM table_name
WHERE column_name LIKE 'pattern'

The LIKE operator uses the following wildcard characters to specify a pattern:

These wildcard characters can be used individually or in combination with each other in the LIKE operator.

For the demo purpose, we will use the following Employee tables in all examples.

Consider the following query with the LIKE operator.

SELECT *
FROM Employee
WHERE FirstName LIKE 'john';

Above, WHERE FirstName LIKE 'john' retrieves all the records where the value in the FirstName column is 'john' or 'John' in MS SQL Server, SQLite, and MySQL database. However, the LIKE operator is case-sensitive in Oracle and PostgreSQL database, so it only fetches records where the value is 'john', not 'John'. The following is the result in MS SQL Server, SQLite, and MySQL database.

The wildcard char % specifies any number of characters.

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

The above query fetches all the records where the value of the FirstName column starts with either 'j' or 'J' followed by any number of characters in MS SQL Server, SQLite, and MySQL database. In Oracle or PostgreSQL, it will fetch records starts with 'j' but not 'J'. The following is the result in MS SQL Server, SQLite, and MySQL database.

The following query retrieves data where FirstName value is '%a%'. It means the value must contain 'a' in any position.

SELECT *
FROM Employee
WHERE FirstName LIKE '%a%';

The above query will display the following result.

The bellow query will return records whose FirstName value contains three letters and 'e' at the second position. The '_' indicates any one character.

SELECT *
FROM Employee
WHERE FirstName LIKE '_e_';

The bellow query uses the [] wildcard pattern.

SELECT *
FROM Employee
WHERE FirstName LIKE 'A[i,m,t,y,s]';

The bellow query uses the [^] wildcard pattern.

SELECT *
FROM Employee
WHERE FirstName LIKE 'A[^i,m,t,y,s]';

The following table list comman patterns used with the LIKE operator:

NOT LIKE

Use the NOT operator with the LIKE operator to filter records that do not match with the specified string.

SELECT *
FROM Employee
WHERE FirstName NOT LIKE 'j%';

Above, FirstName NOT LIKE 'j%' retrieves records where the FirstName values not start with 'j'.