If you ever cross with a spanish last name with spaces, you might have problems with the 8K splitter.

This formula might have its own problems, but it's another option for you.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

SomeID int identity,

FullName varchar(25)

)

insert #Something

select 'DOE,JOHN' union all

select 'DOE,JOHN A' union all

select 'DE LA VEGA,ELENA'

SELECT SUBSTRING( FullName, CHARINDEX(',', FullName) + 1, ISNULL(NULLIF(CHARINDEX(' ', FullName, CHARINDEX(',', FullName)), 0), 8000) - (CHARINDEX(',', FullName) + 1))

,CHARINDEX(',', FullName) + 1

,ISNULL(NULLIF(CHARINDEX(' ', FullName, CHARINDEX(',', FullName)), 0), 8000) - (CHARINDEX(',', FullName) + 1)

FROM #Something