REGEXP_LIKE Function in SQL Server 2025

Problem

I need to do some data validation in our SQL Server database. However, the validation rules are too complex for the T-SQL LIKE function, and I can’t seem to get it done either with PATINDEX or something similar. I’d like to use regular expressions as they’re more powerful. SQL Server 2025 now has a regex function regexep_like to use regular expressions.

Solution

SQL Server 2025 has been released in public preview (at the time of writing, SQL Server 2025 CTP 2.1 is available for download). One of its new features is the support for regular expressions (commonly abbreviated as “regex”). From the geeksforgeeks website:

“A regular expression is a sequence of characters that define a search pattern”

This pattern is similar to a pattern you can define in the T-SQL LIKE function, but the possibilities are much more powerful and, unfortunately, also more complex. There’s a long-running joke that if you can solve a problem with regex, you actually have two problems, because a good regex expression can be very hard to write. Explaining how regex expressions work is out of this tip’s scope. However, there are online tutorials to learn how to write them. You can also use online validators to test your regex expressions. Additionally, there’s a possibility that AI tools, like ChatGPT or CoPilot, can assist you with writing them.

For a long time, SQL Server didn’t have native support for regex. If you wanted to do string validation or search strings using a search pattern, you could use the LIKE function (as explained in this tutorial by Tim Smith) or a function like PATINDEX; however, the patterns they support are not as advanced as true regular expressions. Alternatives options are SQL CLR (.NET integration into the SQL Server database engine), or more the data processing to another tool like Integration Services (which can directly execute .NET scripts).

Note: This tip is written using SQL Server 2025 CTP 2.0. There’s no difference between CTP 2.0 and 2.1 when it comes to regex support. If you want to try out SQL Server 2025 as well, you can use the following tips to get started:

If you want to try out the regex support but the database engine doesn’t have to be SQL Server 2025, the same T-SQL functionality is also available in Azure SQL DB, Azure SQL Managed Instance, and Fabric SQL DB.

REGEXP_LIKE Function

The REGEXP_LIKE function is the LIKE function on steroids. It has the same goal – indicating if a certain pattern is found in a string – but it uses regular expressions instead of the (limited) pattern support of LIKE.

The syntax is:

--MSSQLTips.com
REGEXP_LIKE(string_expression, regular expression [,flags])

Let’s illustrate with an example. Suppose we want to check the validity of the email addresses in the Employee dimension of the AdventureWorks data warehouse. We could use the following regular expression – which I found online – in the REGEXP_LIKE function:

--MSSQLTips.com
SELECT
     EmployeeKey
    ,FirstName
    ,LastName
    ,EmailAddress
FROM dbo.DimEmployee
WHERE REGEXP_LIKE(EmailAddress, '^[\w\-\.]+@([\w-]+\.)+[\w-]{2,}$');

regexp_like with regex to check email address

The query returns 294 employees with a supposedly valid email address. There are 296 rows in the table, which means we can find the 2 “incorrect” emails by adding NOT in front of REGEXP_LIKE:

--MSSQLTips.com
SELECT
     EmployeeKey
    ,FirstName
    ,LastName
    ,EmailAddress
FROM dbo.DimEmployee
WHERE NOT REGEXP_LIKE(EmailAddress, '^[\w\-\.]+@([\w-]+\.)+[\w-]{2,}$');

two false negatives because of accents in the mail address

It seems the two rows are falsely flagged as incorrect, because they have accents in their mail address, which the regex apparently doesn’t take into account. The official documentation uses the following regex expression for emails:

'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

However, it fails to recognize accents as well. This shows how hard it is to write a correct regex, especially for email. These are difficult because they haven’t had a clear definition for a long time, and there are many edge cases. This thread at StackOverflow (How can I validate an email address using a regular expression?) dives deeper into this issue, and there are some very complex regular expressions mentioned. A better validation strategy would be to send the user entering an email address an email with a validation link. Only if this unique link is clicked are you 100% sure you’re dealing with a correct email address.

Optional Flag Parameter

The REGEXP_LIKE function has a third, optional parameter, which allows you to specify a flag. At the time of writing, these values are supported:

i – Case-insensitive
m – Multi-line mode
s – The . matches \n (typically the . in a regex matches any character except line terminators)
c – Case sensitive

Suppose we use this regex to find all first names that start with a capital F:

\bF[a-z].*

This is a very simple example for demonstration purposes. In reality, the LIKE function is much better suited to solve this use case.

With the following script, we change one first name to uppercase to see if it gets selected:

--MSSQLTips.com
UPDATE dbo.DimEmployee
SET FirstName = UPPER(FirstName)
WHERE EmployeeKey = 47; /* Fred --> FRED */

regex returning all first names starting with F

Even though FRED is in all caps and the regular expression specifies that there should be lowercase letters after the initial F, it is still returned by the query. This suggests that the default for regex is case-insensitive, although the documentation states the opposite.

Let’s run the same query, but now with the regex explicitly set to case-sensitive:

names in all caps are filtered out when using case-sensitive regex

This time FRED is omitted from the results. It’s also interesting to notice that “Hung-Fu” is also a match, because the regex sees the “Fu” as a new word. As mentioned before, regex is hard. You can add additional checks (is “F” the first letter of the sentence?) to make the solution more robust. Again, this use case is better solved with LIKE:

Before you start using regex, it’s always a good idea to check if the problem can be solved using non-regex solutions. Also, it’s possible to run into performance issues when using the new regex functions. Check out this blog post by Brent Ozar for more info: T-SQL Has Regex in SQL Server 2025. Don’t Get Too Excited.

Next Steps

Check out the tip Basic Regex Emulator for SQL Server for more regex-goodness in SQL Server.
The tips How to Configure a SQL Server 2025 Demo Environment in Azure and How to Install the AdventureWorks Sample Databases for SQL Server 2025 explain how you can start experimenting with SQL Server 2025.
You can find all SQL Server 2025 related tips in this overview.

Resources

Community

Subscribe