REGEX regular expression functions

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Changes to SQL introduced in SQL Server 2025 Part one of a five-part series of blogs
In addition to handling AI models, vectors of data and JSON, SQL now includes a number of other new functions and features (as this blog explains) REGEX regular expression functions (this blog) Fuzzy string matching Getting the current date (and not the date/time) Using the new UNISTR function to display Unicode characters Encoding binary files as text for transfer using BASE64_ENCODE

Changes to SQL introduced in SQL Server 2025

Part one of a five-part series of blogs

In addition to handling AI models, vectors of data and JSON, SQL now includes a number of other new functions and features (as this blog explains)

REGEX regular expression functions (this blog)
Fuzzy string matching
Getting the current date (and not the date/time)
Using the new UNISTR function to display Unicode characters
Encoding binary files as text for transfer using BASE64_ENCODE

Posted by Andy Brown on 25 November 2025

In this blog

Pre-requisites
An example of a regular expression in a query
The REGEX functions available

If you are a fan of regular expressions, you'll be delighted to know that you can now incorporate them within SQL queries.

Pre-requisites

You can only use the new T-SQL regular expression functions within the latest database format (so compatibility level 170 or higher). One way to find out if you're using this is to run this query:

SELECT

d.name AS 'Database name',

d.compatibility_level AS 'Compatibility level'

FROM

sys.databases as d

ORDER BY

d.name

Here I should be able to use REGEX functions in all of my databases, as I've connected to a database in a SQL Server 2025 instance:

Databases 3 and 4 are ones I've created.

An example of a regular expression in a query

Suppose that you want to list all of the films in a database which might be sequels. A sequel can take various forms - here are examples of each:

Example film title	Test for
Rocky II, Rocky III, Rocky IV	Films ending in II, V, VI or X (upper case only)
Toy Story 2, Toy Story 3, Toy Story 4	Films ending in a digit more than 1, preceded by a space

Here is an example of a query which would show sequels, usig the new REGEXP_LIKE function (the "P" stands for "Pattern", apparently):

SELECT

f.Title,

g.Genre,

f.RunTimeMinutes

FROM

Film AS f

JOIN Genre AS g ON f.GenreID = g.GenreID

WHERE REGEXP_LIKE(

Title,

' [2-9]$|(V|X|II|VI)$'

)

Here are the first few films this would show:

I'm not sure if film number 3 (American History X) and 8 (Super 8) are genuinely sequels, but there's no algorithmic way to filter these out.

Here's what the regular expression bits are doing:

Part of regular expression	What it filters for
' [2-9]$	A space followed by any digit between 2 and 9 appearing at the end of the title
\|	Or (so either of the two critieria could be true)
(V\|X\|II\|VI)$	The title ending in the letters V, X, II or VI

The REGEXP_LIKE function has an optional third argument giving flags. The main one you can use is 'i' to remove case-sensitvity, but by default regular expressions in SQL are case-sensitive.

The REGEX functions available

The new regular expression functions are as follows:

Function	What it does
REGEXP_LIKE	Determines if text matches a regular expression pattern (as shown above by example)
REGEXP_REPLACE	Replaces text where a given regex pattern is found
REGEXP_SUBSTR	Extracts part of a string based on a regular expression pattern
REGEXP_INSTR	Returns the starting or ending position of a matched substring
REGEXP_COUNT	Returns the number of times a regular expression occurs within a string
REGEXP_MATCHES	Returns a table of substrings matching a regular expression
REGEXP_SPLIT_TO_TABLE	Splits a string into a table of substrings using the regex pattern as a delimiter

You can see the full syntax of these functions at this Microsoft site, but most people will probably only need to use the first REGEXP_LIKE function to test if a string matches a regular expression pattern.

Parts of this blog
REGEX regular expression functions (this blog) Fuzzy string matching Getting the current date (and not the date/time) Using the new UNISTR function to display Unicode characters Encoding binary files as text for transfer using BASE64_ENCODE

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

SQL BLOGS

SQL BLOGS

Pre-requisites

An example of a regular expression in a query

The REGEX functions available

Head office

London

Manchester