Bojan Petrovic

SQL Like logical operator introduction and overview

November 13, 2018 by

The SQL Like is a logical operator that is used to determine whether a specific character string matches a specified pattern. It is commonly used in a Where clause to search for a specified pattern in a column.

This operator can be useful in cases when we need to perform pattern matching instead of equal or not equal. The SQL Like is used when we want to return the row if specific character string matches a specified pattern. The pattern can be a combination of regular characters and wildcard characters.

To return a row back, regular characters must exactly match the characters specified in the character string. The wildcard characters can be matched with arbitrary parts of the character string.

Let’s use the AdventureWorks sample database and see some different SQL Like operators with ‘%’ and ‘_’ wildcards.

Using the % wildcard character (represents zero, one, or multiple characters)

The query below returns all telephone numbers that have area code “415” in the “PersonPhone” table:

Notice that ‘415%’ symbol is specified in the Where clause. What this means is that SQL Server will search for the number 415 followed by any string of zero or more characters. Here is the result set:

The Not logical operator reverses the value of any Boolean expression. So, if we just specify Not like with the ‘%’ wildcard character in SQL Like clause, add one additional condition and place it in the same statement as above we should get a query like this:

This time, the query returned all the records in the “PersonPhone” table that have area codes other than 415:

Furthermore, let’s say that we want to find all the records where a name contains “ssa” in them. We can use the query below:

Notice that by using ‘%’ before and after “ssa”, we are telling the SQL Server to find all records in which “Person.Person” has “ssa” characters and it doesn’t matter what other characters are before and after “ssa”:

Using the “_” wildcard character (represents a single character)

The SQL Like underscore character e.g. „ is used when we want to check a single character that can be anything and provide the rest of the characters for our match.

Let’s say that if we want to return all records wherein the “FirstName” table first character can be anything but rest of them should be “en”. Use the query below:

Here is the result set:

Note that a combination of wildcards character can also be used at the end of the search pattern. For example, to return all telephone numbers that have an area code starting with 6 and ending in 2 in the “PersonPhone” table use the following query:

Note that the ‘%’ wildcard character is used after the underscore character since the area code is the first part of the phone number and additional characters exist after in the column value:

Using the [ ] square brackets (any single character within the specified range [a-t] or set [abc])

The SQL Like operator with square brackets is used when we want to have range. Let’s say if we want to find all the rows where “FirstName” first character start with [a-f]. Use the query below:

As it can be seen we have used range [a-f]%. That means to return the first character from a to f and after that, any characters are fine because we used ‘%’ afterward:

To return any single character within a set use the example below to find employees on the “Person” table with the first name of Cheryl or Sheryl:

This query will return only Cheryl in this case, but it would have returned Sheryl as well if we had any records in the database:

Here’s another example when we actually have mixed results:

The above query finds the records for employees in the “Person” table with last names of Zheng or Zhang:

Using [^] square brackets (any single character not within the specified range [a-t] or set [abc])

As you might have guessed, this is the opposite of the previous usage of the SQL Like operator with square brackets. Let’s say that we want to return all the records where “FirstName” first character does not start with [a to f]:

Notice that it only returned the records which do not start with any character from a to f:

With the set example, let’s say that we want to get all the records where “FirstName” does not start with a,d,j. We can use the query below:

Here is the result set:

Using the escape clause

This is one SQL Like predicate that is used to specify an escape character. The query below uses the Escape clause and the escape character:

It returns the exact character string 10-15% in column c1 of the mytbl2 table:

I hope this article on the SQL Like operator has been informative and I thank you for reading.


Bojan Petrovic

Bojan Petrovic

Bojan has a passion for working with computers, servers, and network technology, and electronics. He likes to stay updated with developments and the most recent technology. He currently works at ApexSQL LLC as a Support Engineer with a bachelor’s degree in IT Network and Electronics Technology.

He helps customers with any technical issues and does quality assurance (testing applications and preparing for production) for ApexSQL Complete, ApexSQL Refactor, and ApexSQL Search free add-ins.

View all posts by Bojan Petrovic
Bojan Petrovic

Latest posts by Bojan Petrovic (see all)

T-SQL

About Bojan Petrovic

Bojan has a passion for working with computers, servers, and network technology, and electronics. He likes to stay updated with developments and the most recent technology. He currently works at ApexSQL LLC as a Support Engineer with a bachelor’s degree in IT Network and Electronics Technology. He helps customers with any technical issues and does quality assurance (testing applications and preparing for production) for ApexSQL Complete, ApexSQL Refactor, and ApexSQL Search free add-ins. View all posts by Bojan Petrovic

92 Views