|
|
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/Workaround:
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
|