How to Find Patterns Using SQL LIKE

Individuals who frequently work with data find that they often need to query specific types. SQL provides us with many tools to accomplish this. These tools, such as SQL LIKE, help us look at specific data types instead of the entire database. This reduces the time and effort spent on any given task and gives people the opportunity to work more efficiently.

In this SQL LIKE article, you’ll be learning how to query data to find specific patterns. The following are topics that this article covers: 

  • What is SQL LIKE?
  • The syntax for using LIKE in SQL
  • Wildcard characters used with LIKE
  • LIKE statements and their descriptions
  • Using LIKE on a table's columns

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.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

The Syntax for Using SQL LIKE

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

SELECT column_1, column_2,...column_n

FROM table_name

WHERE column_1 LIKE [pattern];

  • 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.

Statement

Description

WHERE column_name LIKE ‘a%’

Returns any values that start with “a”

WHERE column_name LIKE ‘%sh’

Returns any values that end with “sh”

WHERE column_name LIKE %a%

Returns any values that contain the letter a

WHERE column_name LIKE ‘_’

Returns any values that contain exactly one character

WHERE column_name LIKE ‘__a%’

Returns any values that have “a” as the third character

WHERE column_name LIKE ‘b%n’

Returns any values that start with “b” and end with “n”

WHERE column_name LIKE ‘_%__a’

Returns values that contain at least three characters and end with “a”

WHERE column_name LIKE ‘a___c

Returns values that start with “a” and end with “c”, and contain five characters

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:

like

  • 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:

like-in-sq

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:

select_name_starts_with_output

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

city_starts_and_ends_with-Like_in_SQL

This will result in the following table:

city_starts_and_ends_with_output.

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:

select_city_contains-LIKE_in_SQL

The above query returns the following table:

select-city

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:

ID_contains

This will result in the following:

ID_contains_output

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:

salary_contains-Like_in_SQL

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:

salary_contains_output

  • 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: 

not_like

This will result in the following table:

not_like_output

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:

or_with_like-Like_in_SQL

This will result in the following table:

/or_with_like_output

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.

error

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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.