SQL LIKE Operator

SQL LIKE Operator

This is a detailed tutorial of SQL LIKE Operator. Learn how to select records from the database tables that match a specified pattern fed by you.

Use of SQL LIKE Operator

LIKE operator is used in the SQL WHERE clause. With this operator, you can look into those table records that match the specified pattern fed by you for a particular column. For forming patterns, LIKE operator makes use of two wildcards mentioned below.

%This percent sign is often used to represent characters. Now the characters could be zero, one or as many as possible.
_The underscore symbol will represent a single character of the string.

Note. In the MS Access database, instead of the symbols % and _, you must use the symbols * and ? respectively. In MS Access, * represents zero, one or more characters while ? represents a single character. For more information about it, check out the official guide.

Both of the symbols can be used at the same time in a single pattern formation. Examples will help you to understand the concept better.

Patterns

Let’s try to figure out some patterns that you can form to match strings with it.

PatternDescription
LIKE ‘g%’Filter all records that start with the character ‘g’
LIKE ‘%g’Filter all records that end with the character ‘g’
LIKE ‘%g%’Filter all records that contain the character ‘g’ anywhere in the full string
LIKE ‘_g%’Filter all records that have the character ‘g’ in the second position.
LIKE ‘___g’Filter all records that end with g and has string length 4.
LIKE ‘gu%t’Filter all records that start with ‘gu’ and ends with ‘t’.

This way you can form numerous patterns as per your requirement. You can also use AND, OR and NOT operators to form even more combinations with multiple LIKE operators in the WHERE clause.

LIKE Syntax

The syntax is quite simple as illustrated below.

The above query will select col_1 and col_2 from the database table named table_name for which the records of the col_name column will have the matching pattern. You can use it any of the SQL statements other than the SELECT statement as well. But make you always need to write the LIKE operator in a WHERE clause.

Note. The pattern should be written in quotes.

LIKE Operator Examples

Several examples for the use of LIKE operator are given below to help you understand the concept better. In our demo database, we have got a employees table with lots of records. The quick look of the table is shown below in the screenshot.

LIKE Operator Demo Database

Now, we’ll run different queries with different LIKE operator patterns on this table. The output result set of each of the query is given as a screenshot immediately after the query is written.

Query 1.

The following query selects all records for which the first_name starts with g.

SQL LIKE Starts With A Character Example

Query 2.

The following query selects all records for which the last_name ends with t.

SQL LIKE Operator Ends With A Character Example

Query 3.

The query given below will select all the records that starts with a and has minimum string length 6 in the column last_name.

SQL LIKE Operator String Length Example

Query 4.

LIKE operator used in the following query will filter all the records that contain the sub-string gu in column first_name.

SQL Like Operator SubString Example
Query 5.

SQL Query given below will fetch all the records having the character u in the second position for the column first_name.

SQL LIKE Operator Character Or String At A Particular Position

Query 6.

This query will select all the records for which the column last_name has a value that starts with a and ends with t.

SQL LIKE Operator Starts With & Ends With A Particular Character Example

I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.

Helpful Associated Guide.

Also, don’t forget to Subscribe to WTMatter!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

Your email address will not be published. Required fields are marked *