Press "Enter" to skip to content

Category: Syntax

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.

Leave a Comment

Thoughts on Views

Joe Celko shares some thoughts on views, as well as recursive common table expressions:

VIEWs are an undervalued and underused feature in SQL. They basically consist of a query that has been given a name, and a parameter list, so can be used like an inline macro. Technically, you’re supposed to think of it as a virtual table. 

The idea of an inline macro or inline function goes way back to the first versions of Fortran and later BASIC. Depending on the product, the syntax might look like this: FN DOUBLE (N) = (N + N). You had to give the name of the Macro, a simple optional parameter list, assignment operator, usually an =, and the text of the computation. It was often important to put parentheses around the body of the macro, or to have a compiler that would do this for you. 

Read on for the article and a platform-agnostic coverage of views. My problem with views is that developers try to use them to cover a lot of ruin and then they ask why such a simple SELECT * FROM Something query is so slow.

Leave a Comment

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.

Leave a Comment

Learning RegEx with Louis Davidson

Louis Davidson has a few blog posts for us to catch up on. So far, this is a four-part series on regular expressions and SQL Server.

Part 1 covers simple pattern matching:

I have never once written an regular expression prior to a couple of articles on this blog. And truth be told, when I published those blogs, I got the expression wrong because it seemed to work, and it was what Copilot told me would work. If you are new like me and/or your code is important, test with lots of cases. I obviously fixed that code (thankfully the conclusions were right).

So no, I have never. LIKE does 99% of what I need in a simple manner, and .8% of the time in a complex way, so I never really thought about it too much. I suspect that will be the case even now in SQL, but like any good student, it is time to change my knowledge of regular expressions.

Part 2 covers repeating patterns:

In this blog, I want to look for strings that have 1 or more instances of a repeating pattern. For example, say you want to look for something like the following:

LIKE'%FredFredFred%'

--(or any fixed or unlimited length of a, and only a)
LIKE'%aaaaaaaaaaaaaaa%'or'%aaaaaaa%'

Part 3 looks at matching sets of characters:

In this article, we are going to take an initial look at what are referred to as “character classes” or “character sets” in Regular Expressions. They are commonly used when looking for data to be in a certain format. For example:

We are going to look at how to set a filter for 'lll-ll-lln' and/or 'lll-ll-lll' (where l is letter and n is numeric).

And part 4 deals with negation:

In Part 3, I covered some of the basics of using character classes/sets. (I do tend to say sets.) This allowed us to do things like find words that start with a, b, c, d or e. This is done using: ^[a-e] or ^[abcde]. Now I want to look at two new things (one of which looks really similar to the previous classes but does things very differently.:

  • Negated character classes – Look for strings that don’t have a particular character in them
  • Perl character classes – shorthand for certain types of characters

Regular expressions can be very challenging to learn and even more challenging to troubleshoot and ensure there are no missing corner cases. But they offer an enormous amount of power and that makes it all worthwhile.

Leave a Comment

Common Table Expressions in SQL Server and Materialization

Vlad Drumea is back to following Betteridge’s Law of Headlines and the people rejoice:

There’s this weird misconception floating around LinkedIn and reddit that SQL Server CTEs somehow store results in either memory or tempdb.

This is wrong and whoever states that CTEs store results either have no idea what they’re talking about or are intentionally trying to mislead people for engagement farming.

Click through for the proof of this.

As a quick note, you can materialize common table expressions in some relational database platforms like PostgreSQL, but SQL Server does not have that option.

Leave a Comment

A Deep Dive into IDENTITY Columns

Vlad Drumea performs a deep dive:

In SQL Server, IDENTITY is a column-level property that is used to provide an auto-incremented value for every new row inserted.

All you have to do is provide a seed value and an increment value when defining said column, and SQL Server will handle it from there.

Unlike sequences, identity columns do not require additional objects like default constraints or triggers to ensure the column is populated.

I’m glad that Vlad made a demo showing how @@IDENTITY works and how it can give you unexpected outputs if you’re not aware of a trigger working with a separate identity column. That one tends to get people.

Leave a Comment

The PRODUCT() Function in SQL Server 2025

Ed Pollack points out a new function:

With each version of SQL Server, there are always a few new features introduced that we applaud as we finally have access to a useful function that is already available elsewhere.

Introduced in SQL Server 2025 CTP 1.3, the PRODUCT() function acts similarly to SUM(), but multiplies values rather than adds them. It is an aggregate function in SQL Server and therefore operates on a data set, rather than on scalar values.

Ed notes that there are aggregate and window function versions of PRODUCT() and shows examples of how it works.

Leave a Comment

Grouping Sets in T-SQL

Erik Darling has a new video.

Erik mentions that he doesn’t often see GROUPING SETS in the wild. I’ve used them several times. And the use of the term “several times” probably gives you exactly the feeling that I intended. I really like grouping sets for very specific analytical system purposes (at least for moderate-sized datasets), so I’m glad that syntax is there. But outside of reporting queries, it’s a really uncommon bit of syntax.

Leave a Comment

Percentage Splits with Window Functions

Andy Brownsword breaks things up:

Sometimes you want to segment records. It may be splitting a customer base for marketing purposes, or segmenting a user base for a new feature. Good segmentation makes clean divisions in the data.

In this post we’ll see a way to achieve that with a great deal of help from Window Functions.

Click through for Andy’s motivation, which is a way that absolutely will not work the way you want it to.

Leave a Comment

Result Set Chaining in Snowflake

Kevin Wilkie tries out a new operator:

In a recent Snowflake release, a slick new operator quietly entered the scene: ->>. This little guy can make certain query workflows both more readable and more efficient—especially when you’re dealing with multi-step commands like SHOWLIST, or DESCRIBE.

Click through to see how it works. Seems that this operator has some pretty strict limitations, but for certain use cases, it’s quite nice.

Comments closed