datanew, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

create table staging_tbl_single_row (data varchar(max))
insert into staging_tbl_single_row values
('ABC, RICK SEAN, MD')
,('MES, AEL B, MD FACC')
,('DAN, RK, MD')
,('OHI, NIK, MD F')
,('KA E SYME, PA-C')
,('ALL MUD, SIM, MD')
,('RINE EEMAN, FNP-C'),
('ABC, PQR DIR');

; with cte as 
(
 select 
    row_number() over (order by (select NULL)) as column1,
    column2=
    case
        when 
             (len(data)-len(replace(data,',',''))=2) 
        then 
             data 
        when 
             (len(data)-len(replace(data,',',''))=1) 
        then 
             case --check if space is to the left of comma
                    when 
                        charindex(' ',left(data, charindex(',',data)))<>0
                    then 
                        stuff(data,charindex(' ',data),1,',')
                    else 
                        left(data, charindex(',',data))+
                        stuff(
                            substring(data,
                                      charindex(',',data)+1,
                                      len(data)
                                     ),
                            charindex(' ',
                                      substring(
                                          data,
                                          charindex(',',data)+1,
                                          len(data)
                                          ),
                                      2)
                            ,1,',')
                    end
                    
       when
            (len(data)-len(replace(data,',',''))=0)
        then
              stuff(
       stuff(data,charindex(' ',data),1,',')
        ,charindex(' ',stuff(data,charindex(' ',data),1,','))
       ,1,',')

end,data
    from 
    staging_tbl_single_row
)

select 
    last=[1],first=[2],post=[3]
from 
(
    select 
        t.column1,
        split_values=SUBSTRING( t.column2, t1.N, ISNULL(NULLIF(CHARINDEX(',',t.column2,t1.N),0)-t1.N,8000)),
        r= row_number() over( partition by column1 order by t1.N) 
    from cte t 
        join
        (
            select 
                t.column2,
                1 as N 
            from cte t  
                UNION ALL
            select 
                t.column2,
                t1.N + 1 as N
            from cte t 
                join
                (
                 select 
                    top 8000
                        row_number() over(order by (select NULL)) as N 
                 from 
                    sys.objects s1 
                        cross join 
                   sys.objects s2 
                ) t1 
            on SUBSTRING(t.column2,t1.N,1) = ','
         ) t1
          on t1.column2=t.column2
)a
pivot
( 
    max(split_values) for r in ([1],[2],[3])
   )p

View schema
Execution time: 0,58 sec, rows selected: 8, rows affected: 8, absolute service time: 0,73 sec