When using the like condition to search for symbols which character can you use as a default escape option?

The LIKE operator compares a string expression, such as a column name, with a pattern that uses the wildcard characters % (percent) and _ (underscore). LIKE pattern matching always covers the entire string. To match a sequence anywhere within a string, the pattern must start and end with a percent sign.

LIKE is case-sensitive; ILIKE is case-insensitive.

Syntax

expression [ NOT ] LIKE | ILIKE pattern [ ESCAPE 'escape_char' ]

Arguments

expression

A valid UTF-8 character expression, such as a column name.

LIKE performs a case-sensitive pattern match. ILIKE performs a case-insensitive pattern match for single-byte UTF-8 (ASCII) characters. To perform a case-insensitive pattern match for multibyte characters, use the LOWER function on expression and pattern with a LIKE condition.

In contrast to comparison predicates, such as = and <>, LIKE and ILIKE predicates do not implicitly ignore trailing spaces. To ignore trailing spaces, use RTRIM or explicitly cast a CHAR column to VARCHAR.

A valid UTF-8 character expression with the pattern to be matched.

A character expression that will escape metacharacters characters in the pattern. The default is two backslashes ('\\').

If pattern does not contain metacharacters, then the pattern only represents the string itself; in that case LIKE acts the same as the equals operator.

Either of the character expressions can be CHAR or VARCHAR data types. If they differ, Amazon Redshift converts pattern to the data type of expression.

LIKE supports the following pattern-matching metacharacters:

Examples

The following table shows examples of pattern matching using LIKE:

The following example finds all cities whose names start with "E":

select distinct city from users
where city like 'E%' order by city;
city
---------------
East Hartford
East Lansing
East Rutherford
East St. Louis
Easthampton
Easton
Eatontown
Eau Claire
...

The following example finds users whose last name contains "ten" :

select distinct lastname from users
where lastname like '%ten%' order by lastname;
lastname
-------------
Christensen
Wooten
...

The following example finds cities whose third and fourth characters are "ea". The command uses ILIKE to demonstrate case insensitivity:

select distinct city from users where city ilike '__EA%' order by city;
city
-------------
Brea
Clearwater
Great Falls
Ocean City
Olean
Wheaton
(6 rows)

The following example uses the default escape string (\\) to search for strings that include "start":

select tablename, "column" from pg_table_def 
where "column" like '%start\\_%'
limit 5;

     tablename     |    column
-------------------+---------------
 stl_s3client      | start_time
 stl_tr_conflict   | xact_start_ts
 stl_undone        | undo_start_ts
 stl_unload_log    | start_time
 stl_vacuum_detail | start_row
(5 rows)

The following example specifies '^' as the escape character, then uses the escape character to search for strings that include "start":

select tablename, "column" from pg_table_def 
where "column" like '%start^_%' escape '^' 
limit 5;

     tablename     |    column
-------------------+---------------
 stl_s3client      | start_time
 stl_tr_conflict   | xact_start_ts
 stl_undone        | undo_start_ts
 stl_unload_log    | start_time
 stl_vacuum_detail | start_row
(5 rows)

In this article, we are going to learn how to use the SQL LIKE operator, in SQL Server, using regular expressions to find and/or manipulate text. We will start by learning the symbols and basic syntax of using wildcard regular expressions. We will use character sets and repetition expressions to create flexible matching patterns, and along the way, we’ll examine different ways to use the LIKE operator. And then, finally, in the latter part of the section, we will explore some of the most common and most useful regular expression examples.

SQL is the most commonly used language to work with databases. When you design a report or use BI or any reporting tool, the software is almost certainly building an SQL query behind the scenes which runs on the database and returns your selected data. When we’re looking for specific data or the data that fits specific criteria, the where clause provides the toolset you need. This gives an option to query specific rows that we’re looking for instead of the entire table.

Pre-requisites

Download the AdventureWorks2014 database here to test the following T-SQL samples.

Getting Started

Let us walk-through the SQL statements using the LIKE keyword and wildcard characters. So, let’s get started learning about SQL LIKE operator.

Using SQL LIKE Wildcard Character examples

Regular expressions are patterns for describing how to match strings in a WHERE clause. Many programming languages support regular expressions that use slightly different syntax from what is used with the LIKE operator. In this article, when we refer to regular expressions, we’re referring to the patterns used with the SQL LIKE operator

The following table includes the four different wildcard characters. You can also refer the article SQL string functions for Data Munging (Wrangling) for more examples.

Using SQL LIKE with ‘%’ wildcard character

The following SQL statement returns all of the rows of person table where their last name starts with the letter A. Let us specify the letter ‘A’, the first character that needs to be in the string and then use the wildcard ‘%’, the percent.

You’ll see the output that lists top 10 rows of the person table where the firstname starts with A and the rest of the character is unknown.

Using SQL LIKE with the ‘_’ wildcard character

The wildcard, underscore, is for matching any single character. The following SQL statement finds all telephone numbers that have an area code starting with 7 and ending in 8 in the phonenumber column. We’ve also included % wildcard character at the end of the search pattern as we’re not concerned with the rest of the string values.

The output shows that the area code of that start with 7 and ends with 8 are listed.

Using SQL LIKE with the [ ] wildcard characters

Square brackets e.g [ ] allow us to identify multiple single characters that would be in that particular position. For example, let’s say to list all the rows where first names third character start with I or K. Instead of writing multiple LIKE conditions, we can place the pattern matching set in the third position and close it in the square. The query engine first looks for ‘I’ and then looks for ‘K’.

Let’s execute the following SQL statement

The above query can be re-written using OR condition. It’s more like an OR condition.

In the output, we can see that last names where the third character is ‘I’ or ‘k’ are listed

Using SQL LIKE with the ‘^’ wildcard character

The following SQL statement displays all the rows that do not have the letter that starts with A to D in the first character of their last name. In order to that place the tilde character in the first position of the pattern. It becomes a NOT condition.

Now, if I run the above query, we’ll see that all the names coming back do not have an A, B, C or D as their first character.

Using SQL NOT LIKE with the [] wildcard characters

The following SQL statement finds all the persons where the first name column has more than 3 characters.

The output list only those names where the length of the firstname is more than 3

Using SQL LIKE with the ESCAPE clause

In the following SQL statement, the ESCAPE clause is used to escape the character ‘!’ to negate the meaning of ‘%’ to find the string ‘100% Free’ in the column col1 of the temp table.

The output list only those values where the search pattern ‘100% Free’ matches the col1 expression.

Using SQL LIKE with the CASE statement

The following SQL statement pulls out all of the employees that have a phone number formatted like three-three-four digits with dashes in between (999-999-9999). The pattern is then compared with phonenumber column to derive the domestic or international categories.

The case expression is evaluated for the specific pattern to derive the phone category type.

In the output, we can see the number is classified as domestic or international. The phonenumber column is evaluated with the LIKE operator using the square bracket. The number zero to nine in the first character position is evaluated for matching pattern zero to nine, any number from zero to nine in the second character position and third and then the fourth character position must be a dash and similar logic is applied to the rest of the characters.

Using SQL LIKE with dynamic SQL

The following SQL statement returns all the employees where the lastname matches the pattern Barb. The pattern is dynamically created and compared against the expression.

The output list the matching rows for the specified pattern Barb

Note: By default, CHAR injects trailing blanks depending on the length of the field. Use RTRIM to suppress the trailing blanks, if you’re using the char data-type.

In the following SQL statement, the @eLastName field is of char data type. You can see a use of RTRIM function to trim the trailing blanks.

Using SQL Like with an IF statement

The following SQL statement, the input value is evaluated for the specific pattern in the condition clause using IF statement.

The input string is evaluated for specific patterns using SQL like wildcard expression and returns valid input string.

That’s all for now!

Summary

Thus far, we discussed various tips and four different wildcards (%,_,[], and ^] that are available with the SQL LIKE operator. It is a great searching technique for matching string of characters with the specified patterns or where we’ve not quite sure of what you’re searching aka fuzzy search. The available wildcard characters make the LIKE operator more flexible. I hope you enjoyed this article on the SQL LIKE operator in SQL Server. Feel free ask any questions in the comments below.

• Author
• Recent Posts

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

When using the like condition to search for symbols which character can you use as the default escape option?

Which symbol represents the not equal to condition?

Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?

Which operator is used to combine columns of character strings to other columns || *?