Overview of the SQL LIKE Operator
Overview of the SQL LIKE Operator
Author Recent Posts Prashanth JayaramI’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 Latest posts by Prashanth Jayaram (see all) 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
SQLShack
SQL Server training EspañolOverview of the SQL LIKE Operator
October 2, 2018 by Prashanth Jayaram 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. 123 SELECT TOP 10 *FROM Person.PersonWHERE 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. 1234567 SELECT p.FirstName, p.LastName, PhoneNumberFROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityIDWHERE 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 1234567 SELECT p.FirstName, p.LastName, PhoneNumberFROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityIDWHERE 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. 1234567 SELECT p.FirstName, p.LastName, PhoneNumberFROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityIDWHERE 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 listedUsing 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. 12345 SELECT p.FirstName, p.LastNameFROM Person.Person pWHERE 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. 1234 SELECT DISTINCT firstnameFROM Person.PersonWHERE firstname NOT LIKE '[a-z][a-z][a-z]'; The output list only those names where the length of the firstname is more than 3Using 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. 123456789101112 DROP TABLE IF EXISTS temp;CREATE TABLE temp(col1 VARCHAR(100)); GO INSERT INTO tempVALUES('ApexSQL Refactor is 100% Free SQL Formatter tool'), ('ApexSQL Job is 10-15% off today only'); GO SELECT *FROM TEMP;SELECT *FROM tempWHERE 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. 123456789 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 PhoneNumberFROM Person.PersonPhone AS ph INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityIDORDER 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. 1234567 DECLARE @ELastName VARCHAR(20)= 'Barb';SELECT p.FirstName, p.LastName, a.CityFROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressIDWHERE 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. 1234567 DECLARE @ELastName CHAR(20)= 'Barb';SELECT p.FirstName, p.LastName, a.CityFROM Person.Person p JOIN Person.Address a ON p.BusinessEntityID = a.AddressIDWHERE 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. 12345 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.Author Recent Posts Prashanth JayaramI’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 Latest posts by Prashanth Jayaram (see all) 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