SQL: cross apply to split names into first, last,

I have a table that has user names like this.

Name
-----
Smith-Bay, Michael R.
Abbott, David Jr.
Actor, Cody
Agular, Stephen V.

I need the name to look like:

Last         First    MI
-------------------------
Smith-Bay    Michael  R
Abbott       David    Jr
Actor        Cody
Agular       Stephen  V

I have the following SQL that splits the name into first and last:

select vl.lastname, vf.firstname
from users as t cross apply
(values (left(t.name, charindex(', ', t.name)), stuff(t.name, 1, 
charindex(', ', t.name) + 1, ''))) vl(lastname, rest) 
cross apply 
(values (left(vl.rest, charindex(' ', vl.rest + ' ')))) vf(firstname)
order by  vl.lastname

How can I apply another cross apply to extract basically everything after the first name minus the period at the end?

I've had to do this on many occasions as I work ETL on a regular basis and either need to extract items from within strings due to either bad data storage or just simply having to pull the data from reports. The data isn't always nicely packaged in separate columns and I find myself parsing data for all sorts of reasons. Hopefully the data you are parsing is consistent. Inconsistent data either makes this much more difficult or impossible. If you can rely on your names being exactly in the format you suggested my method below will work perfectly. I've used it on many occasions.

The method below I've used in many different languages. I've done this in MS ACCESS, Microsoft SSMS and C#. My example is out of Oracle.

The basic idea is:

Find the character positions that delimit your First_Name, Last_Name and Middle_Initial strings.

Extract Strings into New Columns using the character positions obtained.

Code is below:

WITH character_pos AS
(
/* First we need the character positions for spaces, commas and the period for the middle initial */
SELECT name
  /* Find 1st Space in the name so we can extract the first name from the string */
  , instr(name, ', ') AS comma_1st_space_pos
  /* Find 2nd Space in the name so we can extract the last name from the string */
  , instr(name, ' ', 1, 2) AS comma_2nd_space_pos
  /* Get the Length of the last name so we know how many characters the substr function should extract */
  , instr(name, ' ', 1, 2) - (instr(name, ', ') + 2) AS last_name_length
  /* Find period in the name so we can extract the Middle Initial should it exist */
  , instr(name, '.')  AS period_pos
  , (instr(name, '.') - 1) - instr(name, ' ', 1, 2) AS middle_initial_length

FROM parse_name
) /* END character_pos CTE */

SELECT name  
  , substr(name, 0, comma_1st_space_pos -1) AS last_name

  , CASE WHEN  period_pos = 0 THEN substr(name, comma_1st_space_pos + 2)
    ELSE substr(name, comma_1st_space_pos + 2, last_name_length) 
    END AS first_name

  , substr(name, comma_2nd_space_pos + 1, middle_initial_length) AS middle_initial

  , comma_1st_space_pos, comma_2nd_space_pos, last_name_length
  , period_pos, middle_initial_length
FROM character_pos
;

I used a CTE just to organize the character positions outside of the actual extraction however this all could be done in one single SQL Statement.

Basically this proves you don't need anything extra outside of just some simple string parsing functions. All you need is Instring and Substring which are usually available in any language. No Stored procedures, no temp table and no extra outside code needed. Unless there are other factors outside the scope of the original question that makes it necessary to use anything other than just SQL.