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.
Wildcard characters | Description |
% | Any string with zero or more characters in the search pattern |
_ | Any single character search with the specified pattern |
[] | Any single character search within the specified range |
[^] | Any single character search not within the specified range |
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.
|
1 2 3 |
SELECT TOP 10 * FROM Person.Person WHERE firstname LIKE 'A%'; |
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.
|
1 2 3 4 5 6 7 |
SELECT p.FirstName, p.LastName, PhoneNumber FROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID WHERE ph.PhoneNumber LIKE '7_8%' ORDER BY p.LastName; |
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
|
1 2 3 4 5 6 7 |
SELECT p.FirstName, p.LastName, PhoneNumber FROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID WHERE ph.PhoneNumber LIKE '7_8%' and p.lastname like 'Ba[ik]%' ORDER BY p.LastName; |
The above query can be re-written using OR condition. It’s more like an OR condition.
|
1 2 3 4 5 6 7 |
SELECT p.FirstName, p.LastName, PhoneNumber FROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID WHERE ph.PhoneNumber LIKE '7_8%' and (p.lastname like 'Bai%' or p.lastname like 'Bak%') ORDER BY p.LastName; |
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.
|
1 2 3 4 5 |
SELECT p.FirstName, p.LastName FROM Person.Person p WHERE LastName LIKE '[^a-d]%' ORDER BY p.lastname; |
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.
|
1 2 3 4 |
SELECT DISTINCT firstname FROM Person.Person WHERE firstname NOT LIKE '[a-z][a-z][a-z]'; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DROP TABLE IF EXISTS temp; CREATE TABLE temp(col1 VARCHAR(100)); GO INSERT INTO temp VALUES('ApexSQL Refactor is 100% Free SQL Formatter tool'), ('ApexSQL Job is 10-15% off today only'); GO SELECT * FROM TEMP; SELECT * FROM temp WHERE col1 LIKE '%100!% Free%' ESCAPE '!'; GO |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT p.FirstName, p.LastName, PhoneNumber, CASE WHEN ph.PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then 'Domestic Phone Number' ELSE 'International Phone number' END PhoneNumber FROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID ORDER BY p.LastName; |
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.
|
1 2 3 4 5 6 7 |
DECLARE @ELastName VARCHAR(20)= 'Barb'; SELECT p.FirstName, p.LastName, a.City FROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressID WHERE p.LastName LIKE '%'+@ELastName+'%'; |
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.
|
1 2 3 4 5 6 7 |
DECLARE @ELastName CHAR(20)= 'Barb'; SELECT p.FirstName, p.LastName, a.City FROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressID WHERE p.LastName LIKE '%'+RTRIM(@ELastName)+'%'; |
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.
|
1 2 3 4 5 |
DECLARE @RuleName NVARCHAR(MAX)= 'SQL Sever 2019 CTP is available for preview'; IF @RuleName LIKE 'SQL Sever [0-9]% CTP is available for preview' PRINT 'valid input good!'; ELSE PRINT 'not a valid good!'; |
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.
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
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021