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