Voice of the DBA

One of the language changes in SQL Server 2025 that I’ve seen a lot of people mention is the addition of RegEx functions to T-SQL. I decided to take a few minutes and try to examine how this feature works, and how I might use it. And more importantly, can AI help?

This is part of a series of experiments with AI systems.

Data with a Bit of a Pattern

One of the common things people use Regex for is validating email addresses.

I created a basic table in a database that looks like this:

CREATE TABLE customer

(
     customerid INT NOT NULL
         CONSTRAINT CustomerPK PRIMARY KEY,
     customeremail VARCHAR(200),
     validated TINYINT

)

GO

I then asked PromptAI to get me some test data like this:

I wasn’t connected to a database, but I still got code generated with insert statements. The AI also noted I had a “validated” column, and it populated that appropriately: 1 for good email, 0 for bad ones.

Once I run the inserts, I have data.

Now, can I check that the AI did this correctly?

REGEXP_LIKE

In SQL Server 2025, there are a number of regular expression functions, and REGEXP_LIKE is one of these. This function is designed to return a boolean if the string_expression matches the pattern_expression, where the latter is the regular expression.

Prompt sees this as a valid function for SQL Server 2025, which is great. I want to use the customeremail from the table as my string to check. For the regex, I need to create a regular expression, something I am not good at doing. I learned to do this at a very rudimentary level when writing Perl, but I’ve lost whatever little skill I used to have.

I saw an expression on StackOverflow for validating email. Let’s check if an AI can help.

In a few seconds, even on airport wi-fi (SFO as I write this), I get a response.

This looks like the expression from the SO answer, though much shorter. If you read SO, you know that the format isn’t as set and stable as we’d like. In any case, let’s see what this shows.

This is interesting. I get an error.

If I look at the docs, this says it returns a true/false, which I’d assume would convert to a 1/0, but let’s try an explicit case. When I do this, it works.

I can also do this in the WHERE clause, where the true/false thing just works. Let me move the function to the WHERE clause. If I do that, I have this code:

SELECT customerid,
        customeremail,
        validated

FROM dbo.customer

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

When I run this, I see what I expect:

Not a bad enhancement to the T-SQL language.

Summary

There’s a lot written on RegEx, so this post isn’t intended to delve deeply into what RegEx expressions you choose. Rather, I wanted to show the basics of this REGEXP_LIKE() function, and showcase one of the weird things I found when including this in the column list. I also haven’t looked at performance yet, though that certainly is something to be concerned about with larger datasets

There are other changes in SQL Server 2025, and I’ll try to examine some in the coming weeks.