Invalid length parameter passed to the RIGHT function MESSAGE

Question

I have an issue. I am running a name parsing script on a file that is 87 million rows. It is set up to parse the NAME column, which has data in it such as SMITH, STEVE S, and so on.

When I run the below command to break up the name into columns firstname, middlename and lastname, which works in all my other tables but this stubborn one, I get this error:

Invalid length parameter passed to the RIGHT function

Not really sure why. Please any help would be great.

UPDATE table6
    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)

May we purchase some sample input and expected output, please? A little formatting of the code would also do wonders. Then we might understand the `else` clause on one of the `case` expressions is based on an imprudent assumption. Or perhaps a NULL snuck in. — HABO, Feb 26 '16 at 02:17
Sure, basically as stated there is a column called NAME that has various names in it in the lastname, firstname middlename format such as: smith, steve ryan smith, steven smith, steve r etc, etc... the above parser splits it to columns firstname, middlename, lastname as per the code... which works great on everything but this table. I do run a cleanup script prior to executing that TSQL script and it is: select top 10 name delete from table6 where CHARINDEX(', ', name) = 0 delete from table6 where name = '' delete from table6 where name is null delete from table6 where NAME like '%,%,' — Sal, Feb 26 '16 at 02:20
So I try to find the bad data and then clean it up.. which I did do.. but still errors. But not every row in the name column has middle name. Bu the script allows for that. I'm thinking since its an error to the RIGHT, is it maybe a firstname (or last) that isn't long enough for the script to recognize it and parse it? — Sal, Feb 26 '16 at 02:26
A name like `WHO, CINDY ` with a trailing blank would cause that error, but it couldn't be that simple. `select Name from Table6 where Name like '% ';` (If you need to trim all Unicode whitespace you can use [this](http://stackoverflow.com/a/35247507/92546) answer.) — HABO, Feb 26 '16 at 02:30
@habo thank you. I will give it a try. Unfortunately it takes a bit on 87 million rows lol. But will let you know what I find. Appreciate the assistance :-) — Sal, Feb 26 '16 at 02:36

score 1 · Answer 1 · answered Feb 26 '16 at 02:33

You probably need to verify that the name is conforms to this syntax. One method is to use like:

UPDATE table6
    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 '% % %';

Invalid length parameter passed to the RIGHT function MESSAGE

1 Answers1