{"id":108153,"date":"2026-01-12T15:07:00","date_gmt":"2026-01-12T15:07:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108153"},"modified":"2026-02-19T19:29:18","modified_gmt":"2026-02-19T19:29:18","slug":"common-sql-server-problems-invalid-length","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/common-sql-server-problems-invalid-length\/","title":{"rendered":"How to fix the Invalid Length error in SQL Server"},"content":{"rendered":"\n

This is another part in my series<\/a> designed to offer guidance around common issues in SQL Server<\/a>. Today, let\u2019s talk about the all-too-common error: invalid length.<\/p>\n\n\n\n

What Is the Invalid Length Error in SQL Server?<\/h2>\n\n\n\n
Msg 537, Level 16, State 3\nInvalid length parameter passed to the LEFT or SUBSTRING function.<\/pre><\/div>\n\n\n\n
The invalid length error, as shown above, occurs when you have incorrect or unexpected input to a string function<\/a>. For example:<\/p>\n\n\n\n
DECLARE @FirstName nvarchar(32) = N'frank';\nSELECT LEFT(@FirstName, -1);<\/pre><\/div>\n\n\n\n
While it\u2019s unlikely you\u2019d intentionally<\/em> 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\u2019s not always true:<\/p>\n\n\n\n
DECLARE @FullName nvarchar(32) = N'Madonna';\nSELECT LEFT(@FullName, CHARINDEX(N' ', @FullName)-1);\n<\/pre><\/div>\n\n\n\n
Despite the text of the error message, this is not isolated to the LEFT<\/code> and SUBSTRING<\/code> functions. It can be triggered by other functions, such as RIGHT<\/code>, 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\u2019t contain LEFT<\/code> or SUBSTRING<\/code>:<\/p>\n\n\n\n
SELECT RIGHT(N'Aaron', -1);<\/pre><\/div>\n\n\n\n
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\u2019s say you are using LEFT<\/code> and SUBSTRING<\/code> and other string functions to split a string, a pattern I\u2019ve seen all too often on Stack Overflow (even in the age of STRING_SPLIT<\/code>):<\/p>\n\n\n\n
SELECT t.val,\n       P1 = LEFT(val, CHARINDEX(N'-', val) - 1),\n       P2 = SUBSTRING(val, CHARINDEX(N'-', val)+1, LEN(val) \n            - CHARINDEX(N'-', REVERSE(val)) - CHARINDEX(N'-', val)),\n       P3 = REVERSE(LEFT(REVERSE(val), CHARINDEX(N'-', REVERSE(val)) - 1))\n  FROM (SELECT N'SAM-134-226' AS val) AS t;<\/pre><\/div>\n\n\n\n
This is more complex than I would write it, but it works fine \u2013 for certain definitions of \u201cfine\u201d \u2013 until val<\/code> contains fewer than two hyphens (try with 'SAM-134'<\/code> or just 'SAM'<\/code>). When you have to troubleshoot such an expression, it can be daunting.<\/p>\n\n\n\n
How to Fix the Invalid Length Error in SQL Server<\/h2>\n\n\n\n
The fix here is usually to add a WHERE<\/code> 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:<\/p>\n\n\n\n
SELECT sub = LEFT(col, CHARINDEX(N'x', col)-1)\n  FROM \u2026\n WHERE col LIKE N'%x%';<\/pre><\/div>\n\n\n\n
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<\/code> list on all rows, before filtering out the non-qualifying rows via the WHERE<\/code> clause. I have talked about this at length in several Stack Overflow answers:<\/p>\n\n\n
\n