What SQL Server 2025 Added to T-SQL: REGEXP Functions, the Native JSON Type, the || Operator, PRODUCT and CURRENT_DATE

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, CU10 (Cumulative Update 10)) against the series' Bookstore database at compatibility levels 170 and 160.

Guess First

E-mail addresses from the sign-up form are waiting in a staging table. For many years we checked e-mail format in SQL Server with LIKE, because there was no other tool. How many of the six addresses pass this check?

DROP TABLE IF EXISTS dbo.signups;
CREATE TABLE dbo.signups (
    signup_id INT IDENTITY(1,1) PRIMARY KEY,
    email     VARCHAR(254) NOT NULL
);
INSERT INTO dbo.signups (email) VALUES
    ('margaret.hamilton@example.com'),
    ('ada@@example.com'),
    ('grace@example'),
    ('linus@exa mple.com'),
    ('.alan@example.com'),
    ('katherine.johnson@example.co.uk');

SELECT email,
       CASE WHEN email LIKE '%_@_%._%' THEN 'pass' ELSE 'fail' END AS like_check
FROM dbo.signups
ORDER BY signup_id;

email                           like_check
------------------------------- ----------
margaret.hamilton@example.com   pass
ada@@example.com                pass
grace@example                   fail
linus@exa mple.com              pass
.alan@example.com               pass
katherine.johnson@example.co.uk pass

Five. The address with a double @, the one with a space and the one starting with a dot also passed; only grace@example, with no dot in the domain, got stuck. LIKE's wildcards (%, _, [...]) cannot go beyond saying "anything here". SQL Server 2025 finally brought regular expression (regex) functions. This post tries them, the native json type, the || operator and 2025's other language additions, again together with their compatibility level requirements.

REGEXP_LIKE and a CHECK Constraint

The same six addresses with a reasonable e-mail regex:

SELECT email,
       CASE WHEN REGEXP_LIKE(email, '^[A-Za-z0-9_%+-]+(\.[A-Za-z0-9_%+-]+)*@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)*\.[A-Za-z]{2,}$')
            THEN 'pass' ELSE 'fail' END AS regex_check
FROM dbo.signups
ORDER BY signup_id;

email                           regex_check
------------------------------- -----------
margaret.hamilton@example.com   pass
ada@@example.com                fail
grace@example                   fail
linus@exa mple.com              fail
.alan@example.com               fail
katherine.johnson@example.co.uk pass

This time only the two addresses that really look valid passed. My goal when writing the regex was not "all of RFC (Request for Comments) 5322" but "catch the obvious mistakes": letters, digits and a few symbols in the local part, no empty parts between dots, at least one dot after @ and an extension of at least two letters. The last word in e-mail validation is always the verification e-mail.

REGEXP_LIKE is a boolean predicate, so it can be used directly in WHERE and in a CHECK constraint. Trying to add the constraint to the existing table:

ALTER TABLE dbo.signups ADD CONSTRAINT CK_signups_email
    CHECK (REGEXP_LIKE(email, '^[A-Za-z0-9_%+-]+(\.[A-Za-z0-9_%+-]+)*@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)*\.[A-Za-z]{2,}$'));

Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "CK_signups_email". The conflict occurred in database "sqlt_18", table "dbo.signups", column 'email'.

As we saw in post 04 of this series, SQL Server checks the existing data when adding a new constraint. I cleaned up the invalid rows, added the constraint and tried sending a new invalid address:

DELETE FROM dbo.signups WHERE signup_id IN (2, 3, 4, 5);
ALTER TABLE dbo.signups ADD CONSTRAINT CK_signups_email
    CHECK (REGEXP_LIKE(email, '^[A-Za-z0-9_%+-]+(\.[A-Za-z0-9_%+-]+)*@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)*\.[A-Za-z]{2,}$'));
INSERT INTO dbo.signups (email) VALUES ('grace@example');

Msg 547, Level 16, State 1, Server b8be943222fa, Line 4
The INSERT statement conflicted with the CHECK constraint "CK_signups_email". The conflict occurred in database "sqlt_18", table "dbo.signups", column 'email'.
The statement has been terminated.

The rule now lives in the database. It does not make validation on the application side unnecessary (the Model Validation post of the ASP.NET Core Web API (Application Programming Interface) series), but it also catches data arriving by a different route (bulk imports, hand-written scripts).

An important difference to know: the regex functions do not honour collation. Microsoft Learn states this explicitly; matching follows the pattern, not collation rules. The Bookstore database uses a case-insensitive collation, and the address Alan.Turing@Example.com starts with a capital letter:

SELECT COUNT(*) AS like_matches  FROM dbo.customers WHERE email LIKE 'alan.%';
SELECT COUNT(*) AS regex_default FROM dbo.customers WHERE REGEXP_LIKE(email, '^alan\.');
SELECT COUNT(*) AS regex_i_flag  FROM dbo.customers WHERE REGEXP_LIKE(email, '^alan\.', 'i');

like_matches
------------
           1
regex_default
-------------
            0
regex_i_flag
------------
           1

LIKE found it thanks to the collation, the regex did not because it is case-sensitive by default, and it did with the 'i' flag. For the same reason, language-specific rules such as the Turkish İ/ı do not apply in regex. SQL Server's regex engine is RE2 (Google's open-source regular expression library); because RE2 does not backtrack, it does not carry risks such as a badly written pattern running in exponential time (ReDoS (Regular expression Denial of Service)), but in return it does not support backreferences and lookarounds.

REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_COUNT

Normalising phone numbers, that is deleting everything except digits and +, meant a chain of nested REPLACE calls before 2025:

SELECT customer_id, phone,
       REGEXP_REPLACE(phone, '[^0-9+]', '') AS phone_normalized
FROM dbo.customers
WHERE phone IS NOT NULL AND customer_id <= 12
ORDER BY customer_id;

customer_id phone             phone_normalized
----------- ----------------- ----------------
          4 +90 555 017 95 53 +905550179553
          5 +90 555 012 91 77 +905550129177
          9 +90 555 011 15 94 +905550111594
         11 +90 555 011 68 88 +905550116888
         12 +90 555 011 62 73 +905550116273

REGEXP_SUBSTR returns the matching part, REGEXP_INSTR its position, and REGEXP_COUNT the number of matches. Extracting the domain from an e-mail address:

SELECT TOP (4) email,
       REGEXP_SUBSTR(email, '@(.+)$', 1, 1, '', 1) AS domain,
       REGEXP_INSTR(email, '@')                    AS at_position,
       REGEXP_COUNT(email, '[0-9]')                AS digits
FROM dbo.customers
ORDER BY customer_id;

email                          domain      at_position digits
------------------------------ ----------- ----------- ------
ada.lovelace@example.com       example.com          13      0
Alan.Turing@Example.com        Example.com          12      0
grace.hopper+books@example.org example.org          19      0
linus.torvalds@example.com     example.com          15      0

The last argument of REGEXP_SUBSTR is the capture group number: I took the parenthesised group in the pattern @(.+)$, that is, everything after @. The arguments in the middle are the start position (1), which occurrence (1) and the flags (empty).

REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE

Two of the regex functions are table-valued: they produce rows from a string. Splitting the subtitle of Design Patterns into words on whitespace:

SELECT b.title, s.value AS word, s.ordinal
FROM dbo.books AS b
CROSS APPLY REGEXP_SPLIT_TO_TABLE(b.subtitle, '\s+') AS s
WHERE b.book_id = 60
ORDER BY s.ordinal;

title           word            ordinal
--------------- --------------- -------
Design Patterns Elements              1
Design Patterns of                    2
Design Patterns Reusable              3
Design Patterns Object-Oriented       4
Design Patterns Software              5

STRING_SPLIT works with a single-character separator; REGEXP_SPLIT_TO_TABLE splits on a pattern like "one or more spaces" and always gives the sequence number. REGEXP_MATCHES returns each match as a row; extracting numbers from free text:

SELECT match_id, match_value
FROM REGEXP_MATCHES('Order 380 shipped with 2 items for 940.80 TRY', '[0-9]+(\.[0-9]+)?');

match_id match_value
-------- -----------
       1         380
       2           2
       3      940.80

Regex and Performance

The cost of regex comes from two places: running the pattern for every row, and not being able to use an index. We can see the second in the plan. There is a UQ_customers_email unique index on customers.email. LIKE 'alan.%':

SET SHOWPLAN_TEXT ON;
GO
SELECT customer_id FROM dbo.customers WHERE email LIKE 'alan.%';
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Seek(OBJECT:([sqlt_18].[dbo].[customers].[UQ_customers_email]), SEEK:([sqlt_18].[dbo].[customers].[email] >= 'alan-þ' AND [sqlt_18].[dbo].[customers].[email] < 'alan/'),  WHERE:([sqlt_18].[dbo].[customers].[email] like 'alan.%') ORDERED FORWARD)

A LIKE with a fixed start turns into an index seek; that is the sargability rule from post 07 of this series. What surprised me was that a regex anchored at the start (beginning with ^) also turned into a seek:

SET SHOWPLAN_TEXT ON;
GO
SELECT customer_id FROM dbo.customers WHERE REGEXP_LIKE(email, '^alan\.', 'i');
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Seek(OBJECT:([sqlt_18].[dbo].[customers].[UQ_customers_email]), SEEK:([sqlt_18].[dbo].[customers].[email] >= 'alan-þþ' AND [sqlt_18].[dbo].[customers].[email] < 'alan/'),  WHERE:(regexp_like([sqlt_18].[dbo].[customers].[email],'^alan\.','i','alan-þþ','alan/',(30))) ORDERED FORWARD)

The optimizer derived a range from the pattern's fixed prefix (alan.), seeked, and applied the regex only to the rows in that range. With an unanchored pattern, the whole index is scanned:

SET SHOWPLAN_TEXT ON;
GO
SELECT customer_id FROM dbo.customers WHERE REGEXP_LIKE(email, 'turing', 'i');
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Scan(OBJECT:([sqlt_18].[dbo].[customers].[UQ_customers_email]),  WHERE:(regexp_like([sqlt_18].[dbo].[customers].[email],'turing','i',NULL,NULL,(1))))

The rule is the same as for LIKE: if the start is fixed, an index may help; if not, every row is read and the regex engine runs for every row. On large tables, instead of making regex the only filter condition, narrow the row count first with a cheap condition (date range, status). One more limit: varchar(max)/nvarchar(max) inputs are supported but limited to 2 MB.

The Native json Type and JSON Aggregates

Before 2025, JSON (JavaScript Object Notation) was stored in nvarchar(max), protected by an ISJSON CHECK constraint. 2025's json type stores the data in a binary format, and the type itself guarantees validity:

DROP TABLE IF EXISTS dbo.book_events;
CREATE TABLE dbo.book_events (
    event_id INT IDENTITY(1,1) PRIMARY KEY,
    payload  JSON NOT NULL
);
INSERT INTO dbo.book_events (payload) VALUES
    ('{"type":"price_changed","bookId":26,"old":390.50,"new":420.00}'),
    ('{"type":"stock_low","bookId":18,"quantity":0}');
SELECT event_id,
       JSON_VALUE(payload, '$.type')   AS event_type,
       JSON_VALUE(payload, '$.bookId') AS book_id
FROM dbo.book_events
ORDER BY event_id;

event_id event_type    book_id
-------- ------------- -------
       1 price_changed      26
       2 stock_low          18

Trying to insert broken JSON (note the trailing comma):

INSERT INTO dbo.book_events (payload) VALUES ('{"type":"stock_low","bookId":18,}');

Msg 13609, Level 16, State 9, Server b8be943222fa, Line 1
JSON text is not properly formatted. Unexpected character '}' is found at position 32.

In an nvarchar variable the same text is accepted silently; you only learn about its validity if you ask:

DECLARE @text NVARCHAR(MAX) = N'{"type":"stock_low","bookId":18,}';
SELECT ISJSON(@text) AS is_valid_text;

is_valid_text
-------------
            0

A native JSON type entered the standard with SQL:2023 (T801). 2025's answer to the "put a variable number of authors into an array" problem from the last exercise of the previous post is JSON_ARRAYAGG:

SELECT b.book_id,
       JSON_OBJECT('title': b.title,
                   'authors': JSON_ARRAYAGG(a.last_name ORDER BY ba.author_order)) AS book_json
FROM dbo.books AS b
JOIN dbo.book_authors AS ba ON ba.book_id = b.book_id
JOIN dbo.authors AS a       ON a.author_id = ba.author_id
WHERE b.book_id IN (42, 60)
GROUP BY b.book_id, b.title
ORDER BY b.book_id;

book_id book_json
------- ----------------------------------------------------------------------------
     42 {"title":"Good Omens","authors":["Pratchett","Gaiman"]}
     60 {"title":"Design Patterns","authors":["Gamma","Helm","Johnson","Vlissides"]}

JSON_ARRAYAGG is an aggregate; with GROUP BY it collected each book's authors into an ordered array. JSON_OBJECTAGG builds an object from key/value pairs:

SELECT JSON_OBJECTAGG(status: order_count) AS orders_by_status
FROM (SELECT status, COUNT(*) AS order_count FROM dbo.orders GROUP BY status) AS s;

orders_by_status
-----------------------------------------------------------------
{"Cancelled":24,"Delivered":345,"Paid":1,"Pending":1,"Shipped":8}

Both aggregates are defined in the SQL/JSON part of the standard in SQL:2016 and have existed in PostgreSQL since 16. Before 2025 the same result needed FOR JSON PATH and string concatenation tricks.

The || Operator, SUBSTRING, PRODUCT and CURRENT_DATE

The standard string concatenation operator || we saw in post 16 now exists in T-SQL (Transact-SQL) and, like +, produces NULL when combined with NULL:

SELECT TOP (3) first_name || ' ' || last_name AS full_name,
       first_name || ' ' || city             AS with_city
FROM dbo.customers
WHERE customer_id IN (1, 6, 7)
ORDER BY customer_id;

full_name      with_city
-------------- --------------
Ada Lovelace   Ada Manchester
Donald Knuth   Donald Berlin
Barbara Liskov NULL

Barbara Liskov's city is NULL, so the second column is NULL. The ||= compound assignment operator came too:

DECLARE @label NVARCHAR(100) = N'Bookstore';
SET @label ||= N' / 2025';
SELECT @label AS label;

label
----------------
Bookstore / 2025

SUBSTRING's third argument is now optional; without it, it takes everything to the end of the string. The standard already defined it this way:

SELECT SUBSTRING(isbn, 4)    AS without_prefix,
       SUBSTRING(isbn, 4, 10) AS old_style
FROM dbo.books
WHERE book_id = 26;

without_prefix  old_style
-------------- ----------
    8900000268 8900000268

PRODUCT is a multiplication aggregate. To compute the overall effect of stacked discounts:

SELECT PRODUCT(factor) AS combined_factor,
       CAST(100 * (1 - PRODUCT(factor)) AS DECIMAL(5,2)) AS total_discount_pct
FROM (VALUES (0.90), (0.95), (0.98)) AS d(factor);

combined_factor total_discount_pct
--------------- ------------------
.837900                      16.21

Discounts of 10%, 5% and 2% add up not to 17% but to 16.21%. It is also useful for growth rates: the product of monthly growth factors is the period's total growth.

WITH monthly AS (
    SELECT DATETRUNC(month, o.ordered_at) AS month_start,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM dbo.orders AS o
    JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled' AND o.ordered_at >= '2025-09-01' AND o.ordered_at < '2026-01-01'
    GROUP BY DATETRUNC(month, o.ordered_at)
),
growth AS (
    SELECT month_start, revenue / LAG(revenue) OVER (ORDER BY month_start) AS factor
    FROM monthly
)
SELECT CAST(PRODUCT(factor) AS DECIMAL(10,4)) AS sep_to_dec_factor
FROM growth
WHERE factor IS NOT NULL;

sep_to_dec_factor
-----------------
           1.2554

The product of the three monthly factors from September to December is 1.2554; that is, December's revenue is 1.2554 times September's. You could also get this with the EXP(SUM(LOG(x))) trick, but that trick breaks on zero and negative values. PRODUCT is not standard.

CURRENT_DATE is the standard's date function; T-SQL did not have it until 2025, and you wrote CAST(GETDATE() AS date):

SELECT CURRENT_DATE                     AS current_date_value,
       CAST(SYSUTCDATETIME() AS date)   AS utc_date_old_style,
       SQL_VARIANT_PROPERTY(CURRENT_DATE, 'BaseType') AS base_type;

current_date_value utc_date_old_style base_type
------------------ ------------------ ---------
2026-11-08         2026-11-08         date

Its type is date. Careful: CURRENT_DATE is computed in the server's local time zone; the container runs on UTC (Coordinated Universal Time), so the two values are the same here. On a server running in Turkey, for three hours after midnight it is one day ahead of the UTC date.

UNISTR, BASE64 and bigint in DATEADD

UNISTR lets you write strings with Unicode code points. To write Turkish characters safely in scripts where you are not sure of the source file's encoding:

SELECT UNISTR(N'\0130stanbul') AS city_name,
       UNISTR(N'K\00FCrk Mantolu Madonna \2014 Sabahattin Ali') AS with_dash;

city_name with_dash
--------- -------------------------------------
İstanbul  Kürk Mantolu Madonna — Sabahattin Ali

BASE64_ENCODE and BASE64_DECODE turn binary data into text and back; before 2025 this needed XML (Extensible Markup Language) tricks:

SELECT BASE64_ENCODE(CAST('Dune:26' AS VARBINARY(20)))       AS encoded,
       CAST(BASE64_DECODE('RHVuZToyNg==') AS VARCHAR(20))      AS decoded;

encoded      decoded
------------ -------
RHVuZToyNg== Dune:26

DATEADD's number argument now accepts bigint. Unix timestamps in milliseconds, such as JavaScript's Date.now(), far exceed the int range; before 2025 you solved this by splitting into seconds and milliseconds with two DATEADD calls. Now it is one step:

DECLARE @js_timestamp_ms BIGINT = 1767225600000;
SELECT DATEADD(millisecond, @js_timestamp_ms, CAST('1970-01-01' AS datetime2(3))) AS utc_time;

utc_time
-----------------------
2026-01-01 00:00:00.000

Production Notes: Preview Functions and Compatibility Level

2025 also brought fuzzy text matching functions: EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE and JARO_WINKLER_SIMILARITY. Microsoft Learn still marks them as preview, and the "What's new" page says they require the PREVIEW_FEATURES database scoped configuration. On my CU10 build they also worked with PREVIEW_FEATURES off, but on the first attempt I hit a different obstacle:

SELECT title, EDIT_DISTANCE(title, N'Dnue') AS distance
FROM dbo.books
WHERE book_id = 26;

Msg 9847, Level 16, State 1, Server b8be943222fa, Line 1
The fuzzy string matching function does not support SQL_* collations. Use a Windows collation instead.

The Bookstore database uses SQL_Latin1_General_CP1_CI_AS, and these functions do not support collations starting with SQL_. After converting the column to a Windows collation:

SELECT TOP (3) title,
       EDIT_DISTANCE(title COLLATE Latin1_General_100_CI_AS, N'Dnue Mesiah')            AS edit_distance,
       JARO_WINKLER_SIMILARITY(title COLLATE Latin1_General_100_CI_AS, N'Dnue Mesiah')  AS jw_similarity
FROM dbo.books
ORDER BY EDIT_DISTANCE(title COLLATE Latin1_General_100_CI_AS, N'Dnue Mesiah');

title        edit_distance jw_similarity
------------ ------------- -------------
Dune Messiah             2            95
İnce Memed               6            65
Emma                     8            56

The book closest to the typo "Dnue Mesiah" is Dune Messiah, at an edit distance of two. Tempting for a "did you mean?" suggestion in a search box, but the preview label means: behaviour and syntax may change before general availability. Do not use them in production; even for experiments, check what is switched on in sys.database_scoped_configurations:

SELECT name, value FROM sys.database_scoped_configurations WHERE name = 'PREVIEW_FEATURES';

name             value
---------------- -----
PREVIEW_FEATURES     0

On the compatibility level side, 2025's rule differs from 2022's. I lowered my database to 160 and tried:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;

SELECT COUNT(*) FROM dbo.customers WHERE REGEXP_LIKE(email, '^alan');

Msg 195, Level 15, State 10, Server b8be943222fa, Line 1
'REGEXP_LIKE' is not a recognized built-in function name.

SELECT REGEXP_REPLACE('+90 555 017 95 53', '[^0-9+]', '') AS works_at_160;

works_at_160
-------------
+905550179553

SELECT 'a' || 'b' AS pipes, PRODUCT(x) AS product_value, SUBSTRING('abcdef', 4) AS substring_value
FROM (VALUES (2), (3)) AS t(x);

pipes product_value substring_value
----- ------------- ---------------
ab                6 def

In my tests at level 160, REGEXP_LIKE, REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE did not work; REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_COUNT did. The json type, the JSON aggregates, ||, PRODUCT, CURRENT_DATE, UNISTR, BASE64_* and the optional SUBSTRING length worked even at level 150. So functions from the same family follow different rules; do not think "I use regex, 160 is enough". A database you upgrade to SQL Server 2025 keeps its old level, and a CHECK constraint containing REGEXP_LIKE fails at deployment with a "not a recognized built-in function name" error. 2025's other big addition, the vector type and the AI (Artificial Intelligence) functions, belongs to the Yapay Zeka (AI) series, not this one.

Common Pitfalls

Assuming regex follows the collation. Even in a case-insensitive database, regex is case-sensitive by default; use the 'i' flag.

Looking for the "perfect" regex for complex formats like e-mail. Catch the obvious mistakes and leave the rest to the verification flow.

Using an unanchored regex as the only filter on a large table. Every row is read and the regex runs for every row.

Deploying a CHECK constraint to an upgraded database without knowing that REGEXP_LIKE requires compatibility level 170.

Still storing JSON in nvarchar(max) without validation. In 2025 the json type guarantees validity.

Putting preview functions into production code.

A Small Experiment

★☆☆ Normalise every phone number in the customers table with REGEXP_REPLACE and find the ones that do not match the format ^\+[0-9]{10,15}$ with REGEXP_LIKE.

★★☆ For each category, collect the books into an array of the form [{"id": ..., "title": ...}, ...] with JSON_ARRAYAGG (combine JSON_OBJECT with JSON_ARRAYAGG). Write the result into a new table with a column of type json.

★★★ Race REGEXP_LIKE against LIKE in the same query: on a copy of the customers table grown to 100,000 rows with GENERATE_SERIES, compare logical reads with SET STATISTICS IO ON for anchored and unanchored patterns. Do not measure time; because the container runs under emulation, timings are misleading, logical reads and plan operators are not.

With this post we complete the SQL Temelleri (SQL Fundamentals) series: starting from the relational model, we covered the query language itself, from SELECT, joins, grouping, subqueries, CTEs (Common Table Expressions), window functions and data modification to dialect differences and release features. In the upcoming SQL Server Derinlemesine (SQL Server in Depth) series, which continues with the same Bookstore database, we go behind these queries: indexes, execution plans, statistics, transaction isolation and locks. The series will keep being updated as new SQL Server releases come out.