SUBSTRING() in SQL Server 2022 vs 2025: Optional LENGTH Is Finally Here

SUBSTRING() is one of those T-SQL functions we all use constantly: parsing identifiers, trimming prefixes, extracting parts of file paths, URLs, error messages, or just cleaning up data for reporting. Until now, SQL Server forced us to always provide the third argument (length).

With SQL Server 2025, Microsoft added a small but very practical improvement: the length parameter is now optional. If you omit it, SQL Server returns the substring from the start position to the end of the string.

See official doc: SUBSTRING (Transact-SQL) – optional length argument.

Syntax comparison

SQL Server 2022 (and earlier)

Length is required:

SUBSTRING(expression, start, length)

SQL Server 2025

Length can be omitted:

SUBSTRING(expression, start[, length])

Practical T-SQL: 2022 vs 2025

Example 1: Extract everything after a prefix

Let’s say you have a standard prefix and you want everything after it.

SQL Server 2022

DECLARE @s varchar(100) = 'ENV:prod-west-eu'; 

-- Get everything after 'ENV:'
SELECT SUBSTRING(@s, 5, LEN(@s) - 4) AS tail_2022;

SQL Server 2025

DECLARE @s varchar(100) = 'ENV:prod-west-eu'; 

-- Get everything after 'ENV:' (length omitted) 
SELECT SUBSTRING(@s, 5) AS tail_2025;

Example 2: Split at a delimiter (common pattern)

Extract everything after the first colon (:).

SQL Server 2022

DECLARE @s varchar(100) = 'ServerName:SQL2022-01'; 

SELECT SUBSTRING(@s, CHARINDEX(':', @s) + 1, LEN(@s)) AS after_colon_2022;

SQL Server 2025

DECLARE @s varchar(100) = 'ServerName:SQL2025-01'; 

SELECT SUBSTRING(@s, CHARINDEX(':', @s) + 1) AS after_colon_2025;

One important detail: omitted vs NULL length

The new behavior is triggered only when the third parameter is omitted. If you explicitly pass NULL as length, the result is still NULL (same as older versions).

-- Same behavior (2022 and 2025): returns NULL because length is explicitly NULL
SELECT SUBSTRING('abc123', 4, NULL) AS returns_null;

Conclusion

This change is small, but in daily T-SQL work it’s one of those “finally!” improvements. Anywhere you currently compute remaining length or use overshoot constants, SQL Server 2025 lets you write cleaner and more intention-revealing code.

If you’re maintaining scripts that must run on both SQL Server 2022 and 2025, keep the old 3-argument form for compatibility.