Press "Enter" to skip to content

Category: T-SQL

Multi- and Single-Line Regular Expression Processing in SQL Server

Louis Davidson continues a series on regular expressions in SQL Server:

There are currently only 4 flags that SQL Server supports and they are used to change some of the fundamental ways that the expressions are applied. These flags are:

i – insensitive
c – case sensitive
m – ^ and $ match end of line, not entire string
s – single line, dot matches newline

In Part 6, I covered i and c, no[w] let’s do m and s. These flags are not ones I expect to use all that often, but they are definitely useful to know.

Read on to see how they work, as well as some of the issues Louis ran into along the way.

Comments closed

Rolling Filtered Indexes in SQL Server

Aaron Bertrand only needs recent data:

I recently resolved an issue where a query pulling data from the last 30 days would time out due to the table’s size and the lack of a supporting index. Creating a supporting index is possible, but not ideal; it will be very large and may not be useful for most queries and use cases. I wonder how I could implement a filtered index that follows time and is always limited to the last n days.

Read on to see how Aaron did this. It’s a clever solution and Aaron does make clear the tricky part of filtered indexes: explicit conditions.

Comments closed

Flags in SQL Server Regular Expression Functionality

Louis Davidson continues a series on regular expressions:

In this week’s sixth entry of my learning RegEx series, I am going to do two last intro entries for a while, this one on case sensitivity, and another on multi and single line searches. After this I will move into all of the functions that are available in SQL Server 2025 and Azure SQL (and I will come back if I learn any additional things that we need to cover either right after that, or anytime I learn something new I want to share about RegEx).

Read on to see which flags SQL Server currently supports. Of those, Louis tries out a pair.

Comments closed

Making Leading Wildcard Searches Faster

Brent Ozar flips everything around:

99.9% of you are never gonna need this.

But let’s say you need to run queries with leading (not trailing) wildcards, like this search for all the different national versions of the Encabulator, each of which has different prefixes depending on which government it’s being sold to:

This is indeed a pretty uncommon scenario. I’m pretty sure I’ve only ever needed to do this once. Well, twice, but in one case I couldn’t actually use the REVERSE() function because the column was itself an awful non-deterministic function and this solution wouldn’t work.

Comments closed

SQL Server Regular Expressions with Multiple Matches

Louis Davidson has popped and therefore cannot stop:

The goal of this week’s entry is specifically to show how to see how multiple matches can be viewed using SQL Server’s RegEx, specifically to make the examples clearer (especially in the upcoming entries).

There are several functions that you can use where multiple matches are used as part of the output:

Click through for that list and several examples of relevant functions in action.

Comments closed

Regular Expression-Based String Splitting in SQL Server 2025

Aaron Bertrand splits a string:

SQL Server users have been asking for native regular expression support for over two decades. There are third-party Common Language Runtime (CLR) modules that offer this functionality, but these can be complicated to install and simply aren’t possible in some environments. I want to split a string using a regular expression instead of a static string. Will that be possible in SQL Server 2025, without CLR?

Must not rant about CLR. Must not rant about CLR. Must not rant about CLR. (By the way, if you ever catch me in person, get me going about how CLR got the short end of the stick and how the ‘modern’ forms of the Common Language Runtime in SQL Server are not great.)

Aaron tries out a function built into SQL Server that allows you to split strings into result sets using a regular expression to perform the splitting, and shows off some of the more complicated scenarios that this can solve over a normal STRING_SPLIT() function call.

Comments closed

Randomization of Personally Identifiable Information

Rich Benner tries out a couple of techniques:

The main issue we see in dev environments is that people take a nice little version of their database, a few hundred rows of data per table, and develop on that. This is great for checking that your logic is correct, but not good when it comes to actually deploying the code to production. Suddenly, your nice, pretty code has to deal with millions of rows of data and grinds to a halt because you didn’t write it for big data sets. No, you wrote it for your development system, and it was fast on your machine. We see this a lot.

Rich shows a couple of techniques for data randomization. My biggest challenge to this is that if you need a proper distribution of data, you lose it. Using the telephone number example, if you have lookups or data analysis by area code, randomly generating across every area code would be bad. Also, if your application is smart enough to deal with valid or invalid area codes and exchanges (the middle three digits of the three-three-four phone number style in the US), you generating arbitrary area codes or exchanges might prevent app developers from using the application in the proper way, perhaps requiring them to fix phone numbers after viewing a data entry screen.

In short, there are easier and harder ways to do this, and several factors may push you into the harder way.

Comments closed