Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server 2012 Management Studio Keyboard Shortcuts - Document Window and Browser
Error Messages
Home > SQL Server Error Messages > Msg 536 - Invalid length parameter passed to the substring function.
SQL Server Error Messages - Msg 536 - Invalid length parameter passed to the substring function.

Error Message

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring 
function.

Causes:

This error is caused by passing a negative value to the length parameter of the SUBSTRING, LEFT and RIGHT string functions.  This usually occurs in conjunction with the CHARINDEX function wherein the character being searched for in a string is not found and 1 is subtracted from the result of the CHARINDEX function.

LEFT(@String, CHARINDEX(' ', @String) - 1)

If the character is not found in a string, a space in this example, the CHARINDEX function will return a value of 0.  Subtracting 1 to this will become -1 and using this as the length parameter in the SUBSTRING or LEFT functions will result to this error.

To further illustrate, assume that we want to get the first name from a full name wherein the first name is separated from the last name by a space.  Here's how the query will look like:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'Elvis Presley'

SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]

The result of this query will be Elvis.  Using the same query but with the full name just containing a first name will yield an error:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'Madonna'

SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]
Server: Msg 536, Level 16, State 3, Line 4
Invalid length parameter passed to the substring function.

Solution / Work Around:

To avoid this error, always make sure that you pass a non-negative value to the length parameter of the SUBSTRING, LEFT and RIGHT functions.  If used in conjunction with the CHARINDEX function, you can use the NULLIF function together with the ISNULL function to check if the character separator is found.  If the character separator is not found then the length of the string is passed to the LEFT function so that it will return the whole string as the result.

Here's how the query above that produces the error will look like:

DECLARE @FullName VARCHAR(50)
SET @FullName = 'Madonna'

SELECT LEFT(@FullName, 
ISNULL(NULLIF(CHARINDEX(' ', @FullName) - 1, -1), LEN(@FullName))) AS [FirstName]

Since there's no space in the full name, the whole full name is returned as the first name.

A function can be created to get the first name from a full name separated by a space.

CREATE FUNCTION [dbo].[ufn_GetFirstName] ( @pFullName    VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
RETURN LEFT(@pFullName, ISNULL(NULLIF(CHARINDEX(' ', @pFullName) - 1, -1), LEN(@pFullName)))
END

Assuming that you have a table of artists that has a column for the full name of the artist and you want to get just the first name, the function can be used for this purpose:

SELECT [FullName], [dbo].[ufn_GetFirstName] ( [FullName] ) AS [FirstName]
FROM [dbo].[Artists]
FullName                      FirstName
----------------------------- -------------------
Britney Spears                Britney
Beyoncé                       Beyoncé
Elvis Presley                 Elvis
Eminem                        Eminem
Jennifer Lopez                Jennifer
Madonna                       Madonna
Mariah Carey                  Mariah
Michael Jackson               Michael
Shakira                       Shakira
50 Cent                       50
Related Articles :