T-SQL Regular expression: LIKE Operator and its use-cases

A regular expression is a rule which defines how characters can appear in an expression. It’s a sequence of character or text which determines the search pattern. In SQL databases, selecting values based on regular expressions defined in the WHERE condition can be very useful. The following are a few use cases of how you can use regular expressions.

1. It can be used to identify the data that uses a combination such as a credit or debit card number, email address, or a telephone number.
2. Find specific text patterns or apply a filter on text, numeric, or special character data.
3. It can be used to parse the data in ETL by creating rules for inbound and outbound traffic, finding patterns in code.

Unlike MySQL and Oracle, SQL Server database does not support built-in RegEx functions. However, SQL Server offers built-in functions to tackle such complex issues. Examples of such functions are LIKE, PATINDEX, CHARINDEX, SUBSTRING and REPLACE. We can combine these functions with others and create a sophisticated and more complex query. These queries are difficult to maintain and require more time and effort to develop it if we are querying a large table than these queries can have a huge impact on performance.

In this article, I am going to explain LIKE operator which can be used for pattern matching. Moreover, I will demonstrate the different use cases where we can use LIKE operator to search for data from a table based on a specific pattern.

The LIKE operator uses a combination of matching expression and pattern. The LIKE operator supports the pattern of following valid wildcard characters.

In this article, I am going to demonstrate the following use cases of the LIKE operator.

Prepare Demo Setup

First, create a demo table named “Patient_Addresses.” To do that, execute the following query:

USE DEMODATABASE 
GO 

CREATE TABLE Patient_Addresses
  ( 
     ID       INT IDENTITY(1, 1), 
     TEXTDATA NVARCHAR(MAX) 
  )

Now, execute the following query to insert data in “Patient_Addresses”.

USE [demodatabase]
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'KALOLI GAM TA-KHEDA DIST- KHEDA')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'PATHAR KUVA RELIEF ROADA''BAD')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'TARA APPTS, GURUKUL ROAD AHMEDABAD')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'1278, HOJAVALIGALI GOMATIPUR A`BD')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'DHOLKA')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'KHODIYAR NAGAR BEHRAMPURA A,BAD')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'2/27 ASHPURI SOC. GHODASAR A`BD')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'GHEE KANTA')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES 
( N'GAM; BODIYA TALUKO; LIMADI DIST; SURENDRANAGR')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'ELISE BRIDGE')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'GJ')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'MP')
GO
INSERT [dbo].[Patient_Addresses] ( [Address]) VALUES ( N'Q')
GO

Once data is inserted, execute the following query to review the data.

USE DEMODATABASE 
GO 

SELECT * 
FROM   [PATIENT_ADDRESSES]

Data should look like the following.

Now let me explain the use cases.

Example 1:

For example, I want to populate only those rows which start with PA. To populate the data, we can use regex “[XY]%.“ To do that, execute the following query.

SELECT * 
FROM   PATIENT_ADDRESSES 
WHERE  ADDRESS LIKE '[PA]%'

Following is the output

As you can see in the above image, query retrieved only record where the value of the address column starts with “PA”

Example 2:

For example, I want to populate the only records which start with only two characters. The first character must be “E” and the second character must be “L.” To do that, execute the following query

SELECT * 
FROM   PATIENT_ADDRESSES 
WHERE ADDRESS LIKE '[E][L]% '

Following is the output:

As you can see from the image above, the query retrieved only record where the value of the address column that has “E” as the first character and “L” as the second character.

Example 3

For example, we want to retrieve only those rows which have two characters, and those characters should be between A to Z hence, the query should be written in the following structure:

USE demodatabase 
go 

SELECT * 
FROM   [patient_addresses] 
WHERE  address LIKE '[A-Z][A-Z]'

The following is the output.

As you can see from the image above, the query returns the data, which has precisely two characters, and the value of both characters are between A and Z.

Example 4

Now we want to retrieve data, where the first character will be between K to P and the rest of the string, will be the same. To retrieve such format, use the following structure.

USE DEMODATABASE 
GO 

SELECT * 
FROM   [PATIENT_ADDRESSES] 
WHERE  ADDRESS LIKE '[K-P]%'

Following is the output:

Similarly, we can retrieve data where the last three characters will be “BAD”, and except those characters, the string will remain the same. To do that, execute the following query.

USE demodatabase 
go 
SELECT * 
FROM   [patient_addresses] 
WHERE  address LIKE '%BAD'

If you found these examples useful, continue reading on the blog to learn more about LIKE Operator and its use-cases — https://codingsight.com/10-best-mysql-gui-tools/.

Thank you and stay tuned!