Error: Invalid Length Parameter Passed to the Right Function in Name Parsing Script
I Gotta Problem. When I Run the Below Code I Get an Error Saying: Invalid Length Parameter Passed to the Right Function. What Does That Mean and How Can It Go...
I gotta problem. When I run the below code I get an error saying:
invalid length parameter passed to the right function. What does that mean and how can it go away? Its to parse a name like smith, steve s to steve r smith in columns firstname, middlename and lastname
UPDATE table5
SET lastName = LEFT(Name, CHARINDEX(', ', Name) - 1),
firstname = SUBSTRING(Name,
CHARINDEX(', ', Name) + 2,
CASE
WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) + 1
ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2)
END - CHARINDEX(', ', Name) - 2
),
middlename= RIGHT(Name,
LEN(Name) - CASE
WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name)
ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2)
END
)
the "name" column is varchar (50) and firstname, middlename and lastname columns are also set to varchar (50) . I'm kinda stumped.. thanks in advance
4 Answers
The error is because CHARINDEX() is returning 0 and SUBSTRING() require at number bigger than 0. Your assumption is correct string without middle name will give you error.
Can you check this fiddle and let me know if that is the format you are using for name. Because the Gordon Linoff query seem to be working for select
NOTE:
I update the SqlFiddle with
WHERE CHARINDEX(', ', Name) > 0
My guess is some of your name doesnt have ,
You can validate doing
SELECT Name
FROM test
WHERE CHARINDEX(', ', Name) = 0
You can also check your other CHARINDEX condition with your data to see which one RETURN 0
I would prefer to write a function, Here is the code.
IF object_id(N'udf_NameSplitter', N'FN') IS NOT NULL
DROP FUNCTION udf_NameSplitter
GO
CREATE FUNCTION udf_NameSplitter
(
@FullName VARCHAR(50),
@NameSplitCharacter CHAR(1),
@NamePart CHAR(50) /*FN-firstname,MN-middlName,LN-lastName*/
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT, @NameTblString VARCHAR(50)
DECLARE @NameTbl TABLE (ID INT Identity(1,1),Item NVARCHAR(1000))
SET @StartIndex = 1
IF SUBSTRING(@FullName, LEN(@FullName) - 1, LEN(@FullName)) <> @NameSplitCharacter
BEGIN
SET @FullName = @FullName + @NameSplitCharacter
END
WHILE CHARINDEX(@NameSplitCharacter, @FullName) > 0
BEGIN
SET @EndIndex = CHARINDEX(@NameSplitCharacter, @FullName)
INSERT INTO @NameTbl(Item)
SELECT SUBSTRING(@FullName, @StartIndex, @EndIndex - 1)
SET @FullName = SUBSTRING(@FullName, @EndIndex + 1, LEN(@FullName))
END
SELECT @NameTblString = LTRIM(RTRIM(Item))
FROM @NameTbl
WHERE ID = CASE WHEN @NamePart = 'LN' THEN 1 WHEN @NamePart = 'FN' THEN 2 ELSE 3 END
RETURN (@NameTblString)
END
GO
Test the function with a few scenarios. I think I covered most but its worth having a second look. I would highly recommend doing a select before an update and see if the data is accurate or as expected.
DECLARE @Name VARCHAR(50) = 'lastName , firstname ,middleName '
SELECT
lastName=dbo.udf_NameSplitter(@Name,',','LN'),
firstname=dbo.udf_NameSplitter(@Name,',','FN'),
middleName=dbo.udf_NameSplitter(@Name,',','MN')
Usage with your table
UPDATE table5
SET lastName=LEFT(Name, CHARINDEX(', ', Name) - 1),
firstname=SUBSTRING(Name, CHARINDEX(', ', Name) + 2, CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) + 1 ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END - CHARINDEX(', ', Name) - 2),
middlename=RIGHT(Name, LEN(Name) - CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END)
It means that charindex() is returning 0, which you are passing to another function. One way to fix this is:
UPDATE table5
SET lastName=LEFT(Name, CHARINDEX(', ', Name) - 1),
firstname=SUBSTRING(Name, CHARINDEX(', ', Name) + 2, CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) + 1 ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END - CHARINDEX(', ', Name) - 2),
middlename=RIGHT(Name, LEN(Name) - CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END)
WHERE Name LIKE '% % %';
This ensures that Name has two spaces, which your logic is assuming.
select left(RTRIM(ltrim(C_Subarea_Code)),2) +
right(RTRIM(ltrim(C_Subarea_Code)),len(RTRIM(ltrim(C_Subarea_Code)))-3)
from Tbl_Doc_Stock_Chem_Add_Mst
in sql
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
