How to Find Patterns Using SQL LIKE

What Is SQL LIKE?

The LIKE operator is used to find specific characters in a table’s columns. It is also used to compare columns with the specified values.

Together with the WHERE clause, it determines if a pattern matches specific values in a table. It uses wildcard characters, which are characters used to replace one or more characters in a string, to fulfill this purpose. You can also use LIKE when only a portion of the values in the table is known.

Let’s go over the syntax for this operator.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

The Syntax for Using SQL LIKE

The SQL LIKE operator is usually used with the SELECT command. The syntax of this is as follows:

• The columns to be shown in the result table are specified after the SELECT statement
• The table is specified in the table_name parameter of the FROM statement
• The column that is designated for pattern matching is specified in the WHERE clause
• The pattern forms from specific row selection, which is defined in the [pattern] parameter of the LIKE operator

NOTE: The LIKE operator is case insensitive.

Multiple patterns can be specified with this operator using the “AND” or “OR” keywords. 

Wildcard characters are the most vital tools of this operator, so let’s go over what these are and how they’re used with LIKE.

Wildcard Characters Used With LIKE

Wildcard characters are special symbols and characters used to represent one or more than one character in a string. These are imperative to the LIKE operator, as these enable patterns to be specified.

The two wildcards used with the LIKE operator are as follows:

• % (percent sign) -  Used to represent zero or more characters.
• _ (underscore) - Each underscore represents exactly one character.

To get a clearer picture of both of these wildcards, let’s look at some examples of how these are used in statements.

LIKE Statements and Their Descriptions

It is important to enclose all patterns in single inverted commas; otherwise, the query will return a syntax error.

To get a more in-depth insight on how to use SQL LIKE, let’s query a table.

Using LIKE on a Table’s Columns

Let’s take a sample from the following table:

• From this table, if you want to know the “EmployeeID” of all employees whose names start with the letter “A,” you should use the following query:

You can also use “a” instead of “A,” because the LIKE operator is not case sensitive.

The query above will result in the following table:

• To determine which employee’s city name starts with “b” and ends with “e,” you’ll need to enter the following code:

This will result in the following table:

This shows that the cities returned contain the pattern specified. To see which cities contain only two characters, enter the following code, which includes using two underscores enclosed in inverted commas:

The above query returns the following table:

This reveals that the table contains only two records, whereas the city attribute only contains two characters.

• The LIKE operator can be used on any data type

The following code will help the user find all records containing two-digit EmployeeIDs:

This will result in the following:

This shows that the “Employee” table contains only one record with a two-digit EmployeeID.

The following code is used to target employees with salaries ranging from 50000 to 59000:

With this query, a decimal and two underscores after the decimal have been used. This is because the salaries are one of the decimal data types, along with two digits after the decimal point, and the pattern needs to resemble this representation in the table. 

The above query will return the following table:

• You can also use the “NOT” keyword in front of LIKE to fetch all records that don’t match the pattern specified

For example, if you want to find all records that match employees whose first names start with every other letter except “A,” you’ll enter the following: 

This will result in the following table:

All records that don’t start with “A” show up in the results.

• You can also use the “AND” or “OR” keywords to specify more than one pattern in a single query

For example, to find out which employees’ names start with either “A” or “H,” you’ll enter the following:

This will result in the following table:

All records that show up in the results match the pattern specifications from the original query.  It is also imperative to enclose the pattern in single inverted commas.

The system returns a syntax error if the pattern is not enclosed in single inverted commas.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Next Steps

SQL LIKE makes it simple for users to identify patterns in a table’s columns. It is an easy-to-use, powerful tool available to all people using SQL for managing data.

Now that you know about the LIKE operator, it is time for you to start querying and manipulating data to find different types of patterns with the help of various commands, operators, and classes. If you liked this article and want to get certified, check out our Business Analyst Master’s Program, which covers everything you need to know to become an SQL expert.

Do you have any questions for us? Leave them in the comments section of our “Finding Patterns Using LIKE in SQL” article, and we’ll have our experts in the field answer them for you.