Since Microsoft Ignite and PASS Summit is this week, I figured a quick RegEx post was in order. I will be shocked if SQL Server 2025 is not released this week, and with that release comes the feature I am most excited about, RegEx in SQL Server.

One of the practical uses of RegEx is more powerful filtering. One of the projects I am working on, (very slowly) is sharing some SQL utilities on GitHub, Utilities like looking at the metadata of a table, searching for columns, database sizes, etc. I usually use LIKE to filter data, which lets me simply use an equality search, or I can also do a partial value search when I don’t know exactly what I am looking for.

So I usually do something like this query where I am searching for columns:

DECLARE @column_name_like sysname = 'name';

SELECT OBJECT_SCHEMA_NAME(columns.object_id) AS schema_name,
       objects.name AS object_name,
       type_desc AS object_type,
       columns.name AS column_name
FROM sys.columns
      JOIN sys.objects
         ON COLUMNS.object_id = objects.object_id
WHERE columns.name LIKE @column_name_like
ORDER BY schema_name, object_name, column_name;

This returns all the objects that have a column named 'name' in the database I am in. If I want to see columns that start, end, or contain name, easy enough. This is something I do semi-often with tablenames like 'invoice' and 'invoiceLineItem', which is usually fine with a filter like 'invoice%'. Unfortunately though, there is often a table with that prefix you don’t want to include, like perhaps a table like invoiceStatus. With a LIKE expression , it would be extremely hard to create a single LIKE expression to get 'invoice' and 'invoiceLineItem' but not invoiceStatus. (I actually think it is impossible, but I am not going to say impossible unless I can prove it!)

And what if you want to look at all columns specifically named name, nm, or thename? Or columns that end with name or nm? With a LIKE expression this would not be easy in one expression, but using RegEx functionality, this is pretty easy, because a search can have multiple search expressions using an or construct.

So if I want to search for multiple criteria we now only need a single expression in a parameter to let the user search. For example, say you want to look for:

• columns named exactly 'name'
• columns that start with 'start'
• columns that end with 'low'

With a regular expression, you can simply type a compound criteria using an expression like: '^name$|^start|low$'

DECLARE @column_name_regex sysname = '^name$|^start|low$';

SELECT OBJECT_SCHEMA_NAME(columns.object_id) AS schema_name,
       objects.name AS object_name,
       type_desc AS object_type,
       columns.name AS column_name
FROM sys.columns
      JOIN sys.objects
        ON COLUMNS.object_id = objects.object_id
WHERE REGEXP_LIKE(columns.name, @column_name_regex,'i') 
--insensitive flag just in case target server is case sensitive
ORDER BY schema_name, object_name, column_name;

And yes, at this point that search criteria is getting messy, but it does give you the power to do this without resorting to having multiple criteria (which is messy on its own). Just keep in mind that this will always be a table scan so this should not be your default manner of searching.

Best of both worlds

In fact you could, for these situations where you are building a utility that queries just a small amount of data, add LIKE and Regex parameters:

DECLARE @column_name_like sysname = '%',
        @column_name_regex sysname = '.?'; 
--any value other than /n, which is not allowed in names

SELECT OBJECT_SCHEMA_NAME(columns.object_id) AS schema_name,
       objects.name AS object_name,
       type_desc AS object_type,
       columns.name AS column_name
FROM sys.columns
       JOIN sys.objects
          ON COLUMNS.object_id = objects.object_id
WHERE columns.name LIKE @column_name_like
  AND REGEXP_LIKE(columns.name, @column_name_regex,'i')
ORDER BY schema_name, object_name, column_name;

Now you have the best of both worlds if they don’t want to use RegEx just to look for a simple filter there is LIKE, and RegEx is there if they want to create more complex searches. If you are building this to be very robust, you might add a warning like:

DECLARE @column_name_like sysname = 'filter'
,@column_name_regex sysname = 'filter'

IF @column_name_like  <> '%' AND
@column_name_regex  <> '.?'
PRINT 'Warning: REGEX and LIKE expressions are ANDed together, which may not be what you expect'

This is probably what I will do with my basic tools because most of the time I am just looking for either one table name, or perhaps two tables that the same suffix like invoice and invoiceLineItem.

Summary

I avoided RegEx so long, but the more I learn about them, the more I realize two things.

1. I don’t need RegEx all that often. LIKE does most of what I need.
2. There are a lot of things you can do with RegEx in one line that would have taken 10 or more lines using LIKE, so I am very glad I am learning them, and very glad they are a part of SQL Server now.

Have any other things that you plan to use RegEx in SQL Server for that you couldn’t do before (or at least it was a lot harder to do previously?)