|
|
Error Message
Server: Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING
function.
Causes
The LEFT string function returns the left part of a character string with the specified number of characters. Similarly, the SUBSTRING string function returns part of a character string from a given starting location and with the specified number of characters. The syntax of each string function is as follows:
|
LEFT ( <character_expression>, <length> )
SUBSTRING ( <character_expression>, <start>, <length> )
In both functions, the <character_expression> is an expression of character or binary data while the <length> is a positive integer that specifies how many characters or bytes of the <character_expression> will be returned. In the case of the SUBSTRING string function, the <start> parameter is an integer that specified where the substring starts.
If the value passed to the <length> parameter is negative, this error message will be encountered. Here’s a script that uses the LEFT string function that doesn’t produce this error:
DECLARE @FullName VARCHAR(50)
DECLARE @Length INT
SET @FullName = 'Mickey Mouse'
SET @Length = CHARINDEX(' ', @FullName)
SELECT LEFT(@FullName, @Length - 1) AS [FirstName]
And this is the output of this script.
FirstName
----------
Mickey
Using this script and changing the value of the string being parsed, in this case the Full Name, and assigning it a value that doesn’t have a space in it, then this error will be generated.
DECLARE @FullName VARCHAR(50)
DECLARE @Length INT
SET @FullName = 'Goofy'
SET @Length = CHARINDEX(' ', @FullName)
SELECT LEFT(@FullName, @Length - 1) AS [FirstName]
GO
Msg 537, Level 16, State 2, Line 12
Invalid length parameter passed to the LEFT or SUBSTRING function.
Since there is no space in the value of the @FullName, the CHARINDEX string function will return a value of 0 since it was not able to find the search string (in this case, the space) from the string expression passed. Subtracting 1 from it yields a value of -1, which is not allowed as the length in the LEFT function.
Modifying the script to use the SUBSTRING string function instead of the LEFT string function will also generate the same error message:
DECLARE @FullName VARCHAR(50)
DECLARE @Length INT
SET @FullName = 'Pluto'
SET @Length = CHARINDEX(' ', @FullName)
SELECT SUBSTRING(@FullName, 1, @Length - 1) AS [FirstName]
GO
Msg 537, Level 16, State 2, Line 12
Invalid length parameter passed to the LEFT or SUBSTRING function.
Solution / Work Around:
This error message can easily be avoided by making sure that the integer value passed as the length to either the LEFT substring function or SUBSTRING string function is not negative. One way of checking it within the LEFT or SUBSTRING function is with the use of the CASE function. The CASE function evaluates a list of conditions and returns one of multiple possible result expressions. In this case, all that is needed to be evaluated is the value of the length and make sure that if ever it is negative, a different value will be passed to it. In this particular case, if the space (or full name separator) is not found, the length of the original string is returned.
Here’s how the scripts will now look like with the incorporation of the CASE function within either the LEFT or SUBSTRING function and thus not generating the error:
DECLARE @FullName VARCHAR(50)
DECLARE @Length INT
SET @FullName = 'Goofy'
SET @Length = CHARINDEX(' ', @FullName)
SELECT LEFT(@FullName,
CASE WHEN @Length - 1 < 0
THEN LEN(@FullName)
ELSE @Length - 1 END) AS [FirstName]
GO
DECLARE @FullName VARCHAR(50)
DECLARE @Length INT
SET @FullName = 'Pluto'
SET @Length = CHARINDEX(' ', @FullName)
SELECT SUBSTRING(@FullName, 1,
CASE WHEN @Length - 1 < 0
THEN LEN(@FullName)
ELSE @Length - 1 END) AS [FirstName]
GO
The indentation of the CASE function together with the WHEN, THEN and ELSE are only done for readability and is not required.
|