script para el cabezón!
public
Jun 11, 2024
Never
71
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;