1
CREATE TABLE names_raw (
2
    raw_name VARCHAR(255)
3
);
4
 
5
CREATE TABLE names_parsed (
6
    Firstname VARCHAR(255),
7
    Middle VARCHAR(255),
8
    Lastname VARCHAR(255)
9
);
10
 
11
INSERT INTO names_raw (raw_name) VALUES
12
('Dominique Dunn, NP'),
13
('Kristine Pasco, NP'),
14
('Heather Gilert, PA'),
15
('Michael E. McCleery, PA-C'),
16
('Bernice E. Barnes, NP'),
17
('Lauri J. Lohse, NP'),
18
('Elisha Chistine, FNP'),
19
('Lauren A. Perugini, APRN');
20
 
21
WITH ParsedNames AS (
22
    SELECT 
23
        raw_name,
24
        -- Extraemos el primer nombre
25
        LEFT(raw_name, CHARINDEX(' ', raw_name + ' ') - 1) AS Firstname,
26
		-- Extraemos el lastname según cuantos espacios haya: si son dos, se entiende que no hay middle, de lo contrario nos posicionamos en el segundo espacio y cogemos hasta la coma.
27
		case
28
			when LEN(raw_name) - LEN(REPLACE(raw_name, ' ', '')) = 2 then 
29
				SUBSTRING(raw_name, CHARINDEX(' ', raw_name + ' ') + 1, CHARINDEX(' ', raw_name + ' ', CHARINDEX(' ', raw_name + ' ')+1) - CHARINDEX(' ', raw_name + ' ') -2) 
30
			when LEN(raw_name) - LEN(REPLACE(raw_name, ' ', '')) = 3 then
31
				SUBSTRING(raw_name, 
32
				CHARINDEX(' ', raw_name + ' ', CHARINDEX(' ', raw_name + ' ') + 1) + 1, 
33
				CHARINDEX(',', raw_name + ' ') - CHARINDEX(' ', raw_name + ' ', CHARINDEX(' ', raw_name + ' ') + 1) - 1
34
				)
35
		end AS Lastname,
36
		-- extraemos el middle
37
        case
38
			when LEN(raw_name) - LEN(REPLACE(raw_name, ' ', '')) > 2 then
39
				SUBSTRING(raw_name, CHARINDEX(' ', raw_name + ' ') + 1, CHARINDEX(' ', raw_name + ' ', CHARINDEX(' ', raw_name + ' ')+1) - CHARINDEX(' ', raw_name + ' ') -1) 
40
		end AS middle
41
    FROM names_raw
42
)
43
 
44
 
45
INSERT INTO names_parsed (Firstname, Middle, Lastname)
46
SELECT
47
    Firstname,
48
     Middle, Lastname
49
FROM ParsedNames;
50
 
51
SELECT * FROM names_parsed;