SQL LIKE statement allows you to perform search string of text based on patterns. SQL LIKE statement is used in the WHERE clause of any valid SQL statements such as SELECT, INSERT, UPDATE and DELETE.
SQL provides you two wildcard characters to construct a pattern. They are percentage (%) and underscore (_).
- Percentage (% ) wildcard allows you to match a sequence of any characters including space.
- Underscore ( _ ) wildcard allows you to match any single character.
The syntax of SQL LIKE statement is as follows:
SELECT column1, column2…
FROM table_name
WHERE column LIKE pattern
The data type of column must be alphanumeric in order to use SQL LIKE statement. It could be CHAR, VARCHAR, NVARCHAR… data type.
Let’s take a look at several examples of using SQL LIKE statement and constructing patterns.
Suppose you want to find all employees with the last name starting with D character, you can use perform the following query.
SELECT lastname, firstname
FROM employees
WHERE lastname LIKE 'D%'
lastname firstname
--------- ---------
Davolio Nancy
Dodsworth Anne
The expression ‘D%’ means find all string starting with character ‘D’ and followed by any characters.
To find all employees which have the first name ending with character ‘t’, you can execute the following query.
SELECT lastname, firstname
FROM employees
WHERE firstname LIKE '%t'
lastname firstname
--------- ---------
Leverling Janet
Peacock Margaret
King Robert
The expression ‘%t’ means any string with any characters in any length and ending with character ‘t’.
You can put the wildcard ‘%’ at the beginning and the end of a string to find any string which contains string within those wildcards. For example to find all employees which have last name contain string “ll”, you can execute the following query as follows:
SELECT lastname, firstname
FROM employees
WHERE lastname LIKE '%ll%'
lastname firstname
-------- ---------
Fuller Andrew
Callahan Laura
Two wildcard characters ‘%’ and ‘_’ can combine together to construct a pattern. For example you can find all employees which have last name starting with any single characters, followed by character ‘a’ and followed by any characters. You can use the combination of both wildcard characters. Here is the query to do so:
SELECT lastname, firstname
FROM employees
WHERE lastname LIKE '_a%'
lastname firstname
-------- ---------
Davolio Nancy
Callahan Laura
SQL LIKE statement can also combine with the SQL NOT operator to find all string which does not match the pattern. For example if you want to find all employees which first name is not starting with character ‘D’, you can perform the following query:
SELECT lastname,firstname
FROM employees
WHERE lastname NOT LIKE 'D%'
lastname firstname
--------- ---------
Fuller Andrew
Leverling Janet
Peacock Margaret
Buchanan Steven
Suyama Michael
King Robert
Callahan Laura
In this tutorial, you’ve learned how to use SQL LIKE statement to find string of text which matches a pattern. You’ve learned how to use two wildcard characters: percentage (%) and underscore (_) to construct a pattern for using in SQL LIKE statement.