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!
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!