Tutorialsteacher

Follow Us

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:

PatternDescription
%The % matches zero, one, or multiple characters (capital or small) or numbers.
E.g. 'A%' will matche all string starting with 'A' and followed by any number of characters or numbers.
_The underscore _ sign matches any single character or number.
E.g. 'A_' will match all strings with two chars where the first character must be 'A' and second character can be anything.
[]The [] matches any single character within the specified range in the [].
E.g. 'A[e,l,p]' will match 'Apple', 'Aelp', 'Alep', 'Aple', etc.
[^]The [^] matches any single character except the specified range in the [^]. E.g. 'A[^e,l,p]' will match anything that starts with 'A', but not 'Apple', 'Aelp', 'Alep', 'Aple', etc.

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.

EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
4'Lex''De Haan''[email protected]'150002018-09-8
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-07-14

Consider the following query with the LIKE operator.

SQL Script: 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.

EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25

The wildcard char % specifies any number of characters.

SQL Script: Wildcard %
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.

EmpIdFirstNameLastNameEmailSalaryHireDate
1'John''King''[email protected]'330002018-07-25
2'James''Bond'2018-07-29

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

SQL Script: IN Operator
SELECT * FROM Employee WHERE FirstName LIKE '%a%';

The above query will display the following result.

EmpIdFirstNameLastNameEmailSalaryHireDate
2'James''Bond'2018-07-29
3'Neena''Kochhar''[email protected]'170002018-08-22
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-07-14

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

SQL Script: LIKE Operator
SELECT * FROM Employee WHERE FirstName LIKE '_e_';
EmpIdFirstNameLastNameEmailSalaryHireDate
4'Lex''De Haan''[email protected]'150002018-09-8

The bellow query uses the [] wildcard pattern.

SQL Script: LIKE Operator
SELECT * FROM Employee WHERE FirstName LIKE 'A[i,m,t,y,s]';
EmpIdFirstNameLastNameEmailSalaryHireDate
5'Amit''Patel'180002019-01-25

The bellow query uses the [^] wildcard pattern.

SQL Script: LIKE Operator
SELECT * FROM Employee WHERE FirstName LIKE 'A[^i,m,t,y,s]';
EmpIdFirstNameLastNameEmailSalaryHireDate
6'Abdul''Kalam''[email protected]'250002020-07-14

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

PatternDescription
FirstName LIKE 'john'Returns records whose FirstName value is 'john' or 'JOHN' or 'John' or 'jOhN' or 'JoHn'.
FirstName LIKE 'j%'Returns records whose FirstName value starts with 'j' or 'J' followed by any number of characters or numbers.
FirstName LIKE '%a%'Returns records whose FirstName value contains 'a' or 'A' at any position.
FirstName LIKE 'a%b'Returns records whose FirstName value should start with 'a' or 'A' and last character should be 'b' or 'B'.
FirstName LIKE '_a'Returns records whose FirstName value contains two characters and the second character must be 'a' or 'A'.
FirstName LIKE '_a%'Returns records whose FirstName value contains the second characters 'a' or 'A'.
FirstName LIKE '%a_'Returns records whose FirstName value has the second last character is either 'a' or 'A'.
FirstName LIKE '___'Returns records whose FirstName value must be three characters long.
FirstName LIKE '___%'Returns records whose FirstName value must contain at least three characters or more.
FirstName LIKE 'A[i,m,t]'Returns records whose FirstName value starts from 'A' and followed by any characters specified in [].
FirstName LIKE 'A[^i,m,t]'Returns records whose FirstName value starts from 'A' and should not followed by any characters specified in [].

NOT LIKE

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

SQL Script: NOT IN Operator
SELECT * FROM Employee WHERE FirstName NOT LIKE 'j%';

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

EmpIdFirstNameLastNameEmailSalaryHireDate
3'Neena''Kochhar''[email protected]'170002018-08-22
4'Lex''De Haan''[email protected]'150002018-09-8
5'Amit''Patel'180002019-01-25
6'Abdul''Kalam''[email protected]'250002020-07-14