declare @t table(name varchar(40)) insert into @t select 'Josefina Castaneda' insert into @t select 'Ana Ortega' insert into @t select 'Juan Carlos Ji'select Firstname =left(name,charindex(' ',name)-1), LastName =reverse(left(reverse(name),charindex(' ',reverse(name))-1)), MiddleName=(case when len(name)-len(replace(name,' ',''))=2 then PARSENAME(replace(name,' ','.'),2) else '' end) from @t/* Firstname LastName MiddleName -------------- ------------------ -------------- Josefina Castaneda Ana Ortega Juan Ji Carlos */
declare @t table(name varchar(40)) insert into @t select 'Josefina Castaneda' insert into @t select 'Ana Ortega' insert into @t select 'Juan Carlos Ji'select Firstname =PARSENAME(name,1), LastName =CASE WHEN PARSENAME(name,3) is null then PARSENAME(name,2) else PARSENAME(name,3) end, MiddleName=CASE WHEN PARSENAME(name,3) is not null then PARSENAME(name,2) else NULL end from (select replace(name,' ','.') as name from @t) a/* Firstname LastName MiddleName -------------- ------------------ -------------- Josefina Castaneda NULL Ana Ortega NULL Juan Ji Carlos */
insert into @t select 'Josefina Castaneda'
insert into @t select 'Ana Ortega'
insert into @t select 'Juan Carlos Ji'select
Firstname =left(name,charindex(' ',name)-1),
LastName =reverse(left(reverse(name),charindex(' ',reverse(name))-1)),
MiddleName=(case when len(name)-len(replace(name,' ',''))=2 then PARSENAME(replace(name,' ','.'),2) else '' end)
from
@t/*
Firstname LastName MiddleName
-------------- ------------------ --------------
Josefina Castaneda
Ana Ortega
Juan Ji Carlos
*/
insert into @t select 'Josefina Castaneda'
insert into @t select 'Ana Ortega'
insert into @t select 'Juan Carlos Ji'select
Firstname =PARSENAME(name,1),
LastName =CASE WHEN PARSENAME(name,3) is null then PARSENAME(name,2) else PARSENAME(name,3) end,
MiddleName=CASE WHEN PARSENAME(name,3) is not null then PARSENAME(name,2) else NULL end
from
(select replace(name,' ','.') as name from @t) a/*
Firstname LastName MiddleName
-------------- ------------------ --------------
Josefina Castaneda NULL
Ana Ortega NULL
Juan Ji Carlos
*/