Parse first name from Last, First Middle when an entry doesn't contain a comma

[LisaRR]

Hello - I am trying to parse the first name out of a field that is formatted Last, First Middle. I have a formula that has worked well but am now getting the error "Invalid length parameter passed to the LEFT or SUBSTRING function" because someone created a name without a comma - the name is REFERRING NOT IN SYSTEM. I would like the first name to return no value for this situation.

Here is the formula I am working with:

,CASE WHEN LEFT(RIGHT(TABLE.NAME, 2), 1) <> ' '
THEN LTRIM(SUBSTRING(TABLE.NAME, CHARINDEX(',', TABLE.NAME) + 1, 99))
WHEN CHARINDEX(',', TABLE.NAME) = 0
THEN ' '
ELSE LEFT(LTRIM(SUBSTRING(TABLE.NAME, CHARINDEX(',', TABLE.NAME) + 1, 99)),
LEN(LTRIM(SUBSTRING(TABLE.NAME, CHARINDEX(',', TABLE.NAME) + 1, 99)))
- 2)
END first_name

I believe it is the -2 in the ELSE statement that is causing the problem but I can't find a way around it. Appreciate help from anyone who can get around this error.

Thank you!

 

[Andrzejek]

I wouldn't pay attention to the placement of commas in your field, I would do:[tt]
REPLACE(TABLE.NAME, ',', '')[/tt] so now all commas are gone and Space is the delimiter.

Detect first Space, that's where Last name ends.
If there is another Space in the rest of the field,
then second Space is the end of First name, the rest is Middle name
Else
There is no Middle name, whatever is left in this field is the First name


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

That does not work with last names like Van Buren or Ten Boom, granted a very few.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[LisaRR]

SkipVought is correct, spaces are not a reliable delimiter. But thank you for the response, Andrzejek.

 

[Andrzejek]

So Lisa, did you come up with any other good solution to your problem?
Looks like - if you do not have a comma in the field - you really do not know where the LastName ends... :-(

PS. And there are names like [tt][blue]Van Der Belt Jr.[/blue], Dennis G [/tt]


---- Andy

There is a great need for a sarcasm font.

 

[LisaRR]

If there are no commas, I would like it to file to the Last Name, which I have figured out with this formula:

,CASE WHEN CHARINDEX(',', TABLE.NAME) = 0 THEN TABLE.NAME
WHEN CHARINDEX(',', TABLE.NAME) <> 0 THEN SUBSTRING(TABLE.NAME, 1, CHARINDEX(',', TABLE.NAME) - 1)
END AS last_name

I tried applying the first row of this formula to the first name CASE statement but it didn't clear the error:

,CASE WHEN CHARINDEX(',', TABLE.NAME) = 0 THEN ' '
WHEN LEFT(RIGHT(TABLE.NAME, 2), 1) <> ' '
THEN LTRIM(SUBSTRING(TABLE.NAME, CHARINDEX(',', TABLE.NAME) + 1, 99))
WHEN CHARINDEX(',', TABLE.NAME) = 0
THEN ' '
ELSE LEFT(LTRIM(SUBSTRING(TABLE.NAME, CHARINDEX(',', ser.PROV_NAME) + 1, 99)),
LEN(LTRIM(SUBSTRING(TABLE.NAME, CHARINDEX(',', TABLE.NAME) + 1, 99)))
- 2)
END first_name