SUBSTRING with multiple CHARINDEX options in query

Question

A column I'm trying to extract only the last name from has possible combinations like H.Naude or H. Naude or H Naude and I need just the Naude part. If I use the SUBSTRING method I'm able to get the characters after either the space or the point but how to test for all 3 possibilities? Unfortunately this data comes from an imported entry form so I have no control as to how the data is formatted. Currently I have the following which is only for the space character

SUBSTRING(H.PtsNonFemale, CHARINDEX('' '', H.PtsNonFemale) +1, DATALENGTH(H.PtsNonFemale) - CHARINDEX('' '', H.PtsNonFemale) +1 ) AS Female

Any assistance is much appreciated.

Answer 1

Assuming the data provided covers all use-cases then you can simply test for each case in descending length order of the of the component you want to remove and use the first match (longest) found in the substring.

SELECT H.PtsNonFemale
    , SUBSTRING(H.PtsNonFemale, COALESCE(NULLIF(I.A,0)+6,NULLIF(I.B,0)+2,NULLIF(I.C,0)+1,NULLIF(I.D,0)+1), LEN(H.PtsNonFemale)) AS Female
FROM (
    VALUES
    ('H.Naude'), ('H. Naude'), ('H Naude'), ('A. M. Someone')
) H (PtsNonFemale)
CROSS APPLY (
    VALUES
    (PATINDEX('_. _. %', H.PtsNonFemale), CHARINDEX('. ',H.PtsNonFemale,0), CHARINDEX('.',H.PtsNonFemale,0), CHARINDEX(' ',H.PtsNonFemale,0))
) I (A, B, C, D);

Returns:

PtsNonFemale	Female
H.Naude	Naude
H. Naude	Naude
H Naude	Naude
A. M. Someone	Someone