G

script para el cabezón!

public
Guest Jun 11, 2024 Never 73
Clone
SQL paste1.sql 51 lines (45 loc) | 1.75 KB
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;