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
14 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) > 0My guess is some of your name doesnt have ,
You can validate doing
SELECT Name
FROM test
WHERE CHARINDEX(', ', Name) = 0You can also check your other CHARINDEX condition with your data to see which one RETURN 0
6I 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
GOTest 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_Mstin sql
1
