The SUBSTRING() function has been one of my most often used tools over the years, often to massage data or help display a partial string. While it's been a very useful function, there has been an annoying limitation for me in the past that is removed in SQL Server 2025. This short article looks at the change.

With a new version of SQL Server coming, I wanted to cover some of the T-SQL code changes. This is part of a series on how the T-SQL language is evolving in this version.

Note: some of these changes are already available in the Azure SQL Database editions.

The Classic Substring Function

Many of you will have used SUBSTRING in the past, often to get a partial look at data. For example, I can get a list of employees and a short snippet of the notes about them from the Northwind database. I'm getting their name and the first 30 characters of the notes field.

In an application, I might want to display some data, but not all with some link to get the full notes if needed. I might play around with how much data I retrieve, but this works fine. I might also do something like this. Suppose I want to get just the last name of a person, I can find the break between first and last name and pull the rest of the substring. For example, I might use this query:

SELECT
       CompanyName,
       SUBSTRING(ContactName, CHARINDEX(' ', ContactName), 12) AS ContactLastName,
       ContactName
FROM dbo.Customers;

This gives me the company and last name of contacts. There are potentially flaws here, as this can also give me middle names, but let's assume our data is clean with only first and last (surname) names.

This looks good, right? Well, let's scroll down. If I look at row 75, I see a problem. The last name is listed as " Braunschwei", but in the ContactName field it's "Art Braunschweiger" (first name included). I didn't account for long names in my function call.

Of course, I can fix this. I can include the LEN() of the field with this code:

SELECT
       CompanyName,
       SUBSTRING(ContactName, CHARINDEX(' ', ContactName), LEN(ContactName)) AS ContactLastName,
       ContactName
FROM dbo.Customers;

That's what I've often done, but I need to remember to do this.  If I don't, and try this, I get an error:

A bit annoying.

SQL Server 2025 Fixes This

In SQL Server 2025, I can run the same query as above, but without an error:

If you look at the results above, my line 75 has the full last name. This is because the substring function assumes the length of the string. I don't have to calculate it or supply a random value.

However, what is returned is the length of the string. If I add to my query the LEN() of the results, you can see these vary according to the size of the data. Here is my query:

SELECT CompanyName,
       SUBSTRING(ContactName, CHARINDEX(' ', ContactName)) AS ContactLastName,
       LEN(SUBSTRING(ContactName, CHARINDEX(' ', ContactName))) AS substringLen,
       LEN(ContactName),
       @@version
FROM dbo.Customers;

Here are the results with the lengths included.

This table in Northwind uses nvarchar as the datatypes. Let's experiment with other types. I'll create a new table and get some data for it from the existing table.

CREATE TABLE NewCustomer
(CustomerID INT NOT NULL IDENTITY(1,1) CONSTRAINT NewCustomerPK PRIMARY KEY
, Company VARCHAR(10)
, Contact CHAR(50)
)
GO
INSERT NewCustomer SELECT CustomerID, ContactName FROM dbo.Customers 
GO

Now I will run a similar query as above, getting the ContactName, the substring, and the lengths. The results are the same with a CHAR() column as those above. I don't get the entire size of the substring (with spaces), but rather just the data returned.

This is because the return type is a varchar, not char. You can see this in the SUBSTRING docs.

One caveat in here, is that if you use NULL as a length, meaning you forget to assign a value, you get NULL. SQL Server is interpreting this as the length, not as a missing parameter. You can see this below.

Summary

That's about it for this change. Starting in SQL Server 2025, you can omit the length parameter for SUBSTRING and have it default to all the rest of the characters in the first parameter. This is a varchar/nvarchar returned, so ending spaces aren't counted.

This should simplify code for many of us, and it brings SQL Server on-premises in line with the changes already in Azure.