Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

LisaRR

Technical User
May 2, 2003
31
US
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!
 
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.
 
That does not work with last names like Van Buren or Ten Boom, granted a very few.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
SkipVought is correct, spaces are not a reliable delimiter. But thank you for the response, 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] [lol]


---- Andy

There is a great need for a sarcasm font.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top