A Fine Slice Of SQL Server
Tomaz Kastrun continues an advent of SQL Server 2025. Day 8 looks at a pair of regular expression-related functions:
Continuing with SQL Server 2025 T-SQL functions for Regular Expressions for in string and count functionalities.
Last two functions in the family of new T-SQL functions that were shipped with RegEx, are REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE().
Read on to see how all four of these work.
Comments closedSebastiao Pereira generates some numbers:
Random number generation is vital in computer science, supporting fields like optimization, simulation, robotics, and gaming. The quality, speed, and sometimes security of the generator can directly affect an algorithm’s correctness, performance, and competitiveness. In Python, random number generation is well-supported and widely used. In this article, we will look how to we can use SQL to do this.
Click through for several examples.
Comments closedTomaz Kastrun has been busy with this year’s advent of SQL Server 2025 blog posting. Catching up, Tomaz first looks at base-64 encoding and decoding:
SQL Server 2025 introduces a new T-SQL functions for BASE64 varbinary expressions. The first function returns base64-encoded text (BASE64_ENCODE() ), respectively for BASE64_DECODE().
BASE64_ENCODEconverts the value of a varbinary expression into a base64-encoded varchar expression.
BASE64_DECODEconverts a base64-encoded varchar expression into the corresponding varbinary expression.
I really like this, by the way. Base-64 encoding is quite useful for web transmissions, so having a place to generate that output easily is nice.
SQL Server 2025 introduces a new T-SQL functions for Regular Expressions (RegEx).
With multiple RegEx functions, the LIKE function indicates if the regular expression pattern matches the string or is in a string. The function is REGEXP_LIKE() that will do the job.
And third, we have REGEXP_SUBSTR() and REGEXP_REPLACE():
Continuing with SQL Server 2025 T-SQL functions for Regular Expressions for replace and substring functionalities.
Click through for Tomaz’s thoughts on all five of these functions.
Comments closedLouis Davidson is lost in translation:
The data I am working with sometimes has people with multiple parts to their name (Mary Jo, Cindy Lou) etc, or sometimes Fred/Joe, Mary and Jack, Mary & Jack, or what have you. My goal was to turn these names into little delimited lists that I could parse on a space character with
STRING_SPLITand there were a “few” of these cases. This was the code I had arrived at when I reached the “good enough” stage of my coding.
Louis had 19 nested REPLACE() calls, but Certified Good Guy Erik Darling shows him the way.
SQL Server 2025 introduces a new T-SQL function, called PRODUCT(). The function returns the product of all the numeric (and only) values in the expression. NULL values are ignored and DISTINC values can be set.
Distinct product is an interesting one.
Comments closedErik Darling has a new video. Erik shows how SQL Server handles default constraints that use user-defined functions and how this behaves under a variety of circumstances. There’s also a dive into parallelism and constraints. We also learned Erik’s ability to perform fractional math and how he actually differentiates “scalar” from “scaler,” proving once again that he is not midwestern from his use of extraneous vowel sounds.
1 CommentIn this article, we’ll explore the concept of framing in window functions. We’ll compare the differences between the ROWS and RANGE clauses and discuss when to choose one over the other. We’ll also highlight common pitfalls of framing and whether it applies to all types of window functions. By the end, you’ll better understand how framing works with window functions, making it seem less complex.
Click through for a primer on frames in window functions. Admittedly, if I were writing this article, I’d toss out most of the “pitfalls” section, as pitfalls 2 and 3 aren’t particularly relevant or pitfall-y (because SQL Server always defines a frame on a window function if you don’t). Instead, I’d add that there are some annoying limitations on RANGE frames, where the ANSI SQL standard allows you to use intervals like date or time when defining frames, so you can get records ranging from three hours ago to right now, for example.
But that said, it’s a good overview if you’re fairly new to window functions.
Comments closedSebastiao Pereira generates more artificial data:
Generating random numbers from an exponential distribution is essential for queuing theory, reliability engineering, physics, finance modeling, failure analysis, Poisson process, simulation and Monte Carlo methods, computer graphics, and games. Is it possible to have a Random Exponential Gaussian Numbers function in SQL Server without use of external tools?
As always, I love this series because these examples are complex enough not to be trivial, yet perform well enough to work in real-world environments.
Comments closedKendra Little wants to know what’s happening right now with this query:
I frequently need to see rowcounts and execution time for queries while they’re running. Maybe I’m troubleshooting a slow query that’s still executing, or I want to understand which operators are causing the slowdown before the query completes.
Last week at the PASS Summit I learned some little nuances about how this works that I’d missed.
Click through to learn what Kendra learned (and now what I learned).
Comments closedRajendra Gupta uses a reducer function:
SQL Server 2025 includes new features and enhancements. In the previous SQL Server 2025 tips, we have explored many new features. Have you explored the new Product() function? If not, this will walk you through the Product() function with several examples.
Read on to see how PRODUCT() works and how thoughtful the development team was around supporting window functions as an aggregate function.