In this Microsoft SQL Server 2025 series:
- Dec 01: Microsoft SQL Server 2025 – Introduction and installation
- Dec 02: Microsoft SQL Server 2025 – New T-SQL functions – native JSON data type and some functions
- Dec 03: Microsoft SQL Server 2025 – New T-SQL functions – JSON Index
- Dec 04: Microsoft SQL Server 2025 – New T-SQL functions – Product()
- Dec 05: Microsoft SQL Server 2025 – New T-SQL functions – BASE64_ENCODE() and BASE64_DECODE()
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.
In order to run this, you have to have your SQL Server 2025 database set to compatibility level 170.
USE master;
GO
DROP DATABASE IF EXISTS db_05_regex;
GO
CREATE DATABASE db_05_regex;
GO
-- Just in case
ALTER DATABASE db_05_regex SET COMPATIBILITY_LEVEL = 170;
USE db_05_regex;
GO
Using REGEXP_LIKE with CREATE TABLE
You can use the REGEXP_LIKE for check in create table statement. And it will evaluate and checked with every row inserted into the table.
DROP TABLE IF EXISTS EMPLOYEES;
GO
CREATE TABLE EMPLOYEES (
ID INT IDENTITY(101,1),
[Name] VARCHAR(150),
Email VARCHAR(320)
CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')),
Phone_Number NVARCHAR(20)
CHECK (REGEXP_LIKE(Phone_Number, '^\(\d{3}\) \d{3}-\d{4}$'))
)
And so with the valid or invalid insert, you will get messages accordingly.
-- Valid INSERT
INSERT INTO EMPLOYEES ([Name], Email, Phone_Number)
VALUES ('Tomaz Kastrun', 'tomaz.kastrun@example.com', '(123) 456-7890');
GO
And you will have 1 row inserted, because the email and the phone number follow the correct format. With invalid phone number or email, your inserted will be be executed, since it will have a conflict with the CHECK constraint on phone_number field.
-- Invalid INSERT
INSERT INTO EMPLOYEES ([Name], Email, Phone_Number)
VALUES ('Tom Jones', 'tom.jones@example.com', '123-456-7890');
GO
Using REGEXP_LIKE with SELECT statement
With select statement the REGEXP_LIKE handles the string Expression, the pattern expression and flags.
-- SELECT
SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE(Email, '^[^@]+\.[^.]*exa.*\.com$');
GO
If you want yo enchance the accuracy of the CE (Cardinality estimator) for the REGEXP_LIKE function, Microsoft suggests to use the ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP and ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP query hints.
Tomorrow we will look into the new T-SQL functions for REGEXP_SUBSTR and REGEXP_REPLACE.
As always, the code is available at my Github: https://github.com/tomaztk/SQLServer2025
Happy coding!
TomazTsql 
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike
[…] Dec 06: Microsoft SQL Server 2025 – New T-SQL functions – REGEXP_LIKE() […]
LikeLike