RIGHT of CHARINDEX not selecting correctly

Question

I am trying to parse out a last name field that may have two last names that are separated by either a blank space ' ' or a hyphen '-' or it may only have one name.

Here is what I'm using to do that:

select      top 1000
            BENE_FIRST_NAME, 
            BENE_LAST_NAME,
            FirstNm =
                case
                    when BENE_FIRST_NAME like '% %' then
                        left(BENE_FIRST_NAME, CHARINDEX(' ', BENE_FIRST_NAME))
                    when BENE_FIRST_NAME like '%-%' then
                        left(BENE_FIRST_NAME, CHARINDEX('-', BENE_FIRST_NAME))
                    else BENE_FIRST_NAME
                end,
            LastNm =
                case
                    when BENE_LAST_NAME like '% %' then
                        right(BENE_LAST_NAME, CHARINDEX(' ', BENE_LAST_NAME))
                    when BENE_LAST_NAME like '%-%' then
                        right(BENE_LAST_NAME, CHARINDEX('-', BENE_LAST_NAME))
                    else BENE_LAST_NAME
                end,
            CharIndxDash = CHARINDEX('-', BENE_LAST_NAME),
            CharIndxSpace = CHARINDEX(' ', BENE_LAST_NAME)
from        xMIUR_Elig_Raw_v3

Here are some results:

BENE_FIRST_NAME	BENE_LAST_NAME	FirstNm	LastNm	CharIndxDash	CharIndxSpace
JUANA	PEREZ-MARTINEZ	JUANA	RTINEZ	6	0
EMILIANO	PICENO ESPINOZA	EMILIANO	SPINOZA	0	7
JULIAN	NIETO-CARRENO	JULIAN	ARRENO	6	0
EMILY	SALMERON TERRIQUEZ	EMILY	TERRIQUEZ	0	9

The CHARINDEX seems to be selecting the correct position but it is not bringing in all of the CHARs to the right of that position. Sometimes it works like in the last record. But sometimes it is off by 1. And sometimes it is off by 2. Any ideas?

Answer 1

If you need to select part of a last name after space/hyphen, you need to get right part of the string with length = total_lenght - space_position:

  ...
  LastNm =
            case
                when BENE_LAST_NAME like '% %' then
                    right(BENE_LAST_NAME, LEN(BENE_LAST_NAME) - CHARINDEX(' ', BENE_LAST_NAME))
                when BENE_LAST_NAME like '%-%' then
                    right(BENE_LAST_NAME, LEN(BENE_LAST_NAME) -CHARINDEX('-', BENE_LAST_NAME))
                else BENE_LAST_NAME
            end,
  ...