This is another part in my series designed to offer guidance around common issues in SQL Server. Today, let’s talk about the all-too-common error: invalid length.

What Is the Invalid Length Error in SQL Server?

The invalid length error, as shown above, occurs when you have incorrect or unexpected input to a string function. For example:

While it’s unlikely you’d intentionally pass a negative value here, the desired substring often comes from some other calculation. For example, you might assume that a full name always has a space, but that’s not always true:

Despite the text of the error message, this is not isolated to the LEFT and SUBSTRING functions. It can be triggered by other functions, such as RIGHT, which may make it harder to spot the problem in the code. Imagine being told the error message, then being handed this code, which doesn’t contain LEFT or SUBSTRING:

Still, those are easy to spot, because the -1 gives a clue. What about a more complex expression that is dependent on the data? Let’s say you are using LEFT and SUBSTRING and other string functions to split a string, a pattern I’ve seen all too often on Stack Overflow (even in the age of STRING_SPLIT):

This is more complex than I would write it, but it works fine – for certain definitions of “fine” – until val contains fewer than two hyphens (try with 'SAM-134' or just 'SAM'). When you have to troubleshoot such an expression, it can be daunting.

How to Fix the Invalid Length Error in SQL Server

The fix here is usually to add a WHERE clause, but the error can also occur even though you have explicitly filtered out values that you know will break your expression – for example, if you have:

It may not happen in such a simple query but, in more complex cases, SQL Server might still try to evaluate the expression in the SELECT list on all rows, before filtering out the non-qualifying rows via the WHERE clause. I have talked about this at length in several Stack Overflow answers:

It can make for convoluted code, but one way to avoid this scenario is to force evaluation of the condition using CASE or IIF. For example:

A more self-documenting way would be:

You don’t need to provide an ELSE in either of these scenarios, because the rows that yield NULL will ultimately get filtered out anyway.

You can also do something tricky to yield NULL when the CHARINDEX might be negative, as long as the input string cannot be longer than 255 characters:

Trying to convert to a tinyint will yield NULL when the outcome is negative, since tinyint is constrained to numbers between 0 and 255. However, as mentioned, this has limitations that make it brittle, and is less self-documenting than a CASE expression.

If your query is a lot more complex or you have multiple expressions, or you still can’t get the filter to apply before SQL Server attempts evaluation, you may want to simply materialize the results into a table before attempting string manipulation. For example:

As an aside, there are other functions where you might expect passing a negative length would yield this same error message, but SQL Server more gracefully returns NULL instead:

Final Thoughts & Key Takeaways

Always code defensively. Users, bots, and QA engineers are going to come up with plenty of edge cases, sometimes on purpose – try to think like them.

Someone might inadvertently add a space to their last name, e.g. O’ Brien or D’ Agostino. Or they might legitimately have spaces in their first or last name, e.g. De Wayne van der Wal. Or go by a single name only, like Madonna or {insert the “artist formerly known as Prince” emoji here}.

So don’t, for example, search for the first space and assume there will only be one – or that there will be one at all.