|
|
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
|