1 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
5 family_name TEXT := '';
6 first_given_name TEXT := '';
7 second_given_name TEXT := '';
11 before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
12 suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
14 IF suffix = before_comma THEN
18 family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
19 first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
20 second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
22 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
24 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
26 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
30 family_name TEXT := '';
31 first_given_name TEXT := '';
32 second_given_name TEXT := '';
37 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
38 IF temp ilike '%MR.%' THEN
40 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
42 IF temp ilike '%MRS.%' THEN
44 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
46 IF temp ilike '%MS.%' THEN
48 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
50 IF temp ilike '%DR.%' THEN
52 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
54 IF temp ilike '%JR%' THEN
56 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
58 IF temp ilike '%JR,%' THEN
60 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
62 IF temp ilike '%SR%' THEN
64 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
66 IF temp ilike '%SR,%' THEN
68 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
70 IF temp ~ E'\\sII$' THEN
72 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
74 IF temp ~ E'\\sIII$' THEN
76 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
78 IF temp ~ E'\\sIV$' THEN
80 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
83 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
84 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
85 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
87 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
89 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
91 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
95 family_name TEXT := '';
96 first_given_name TEXT := '';
97 second_given_name TEXT := '';
102 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
103 IF temp ilike '%MR.%' THEN
105 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
107 IF temp ilike '%MRS.%' THEN
109 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
111 IF temp ilike '%MS.%' THEN
113 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
115 IF temp ilike '%DR.%' THEN
117 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
119 IF temp ilike '%JR.%' THEN
121 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
123 IF temp ilike '%JR,%' THEN
125 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
127 IF temp ilike '%SR.%' THEN
129 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
131 IF temp ilike '%SR,%' THEN
133 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
135 IF temp like '%III%' THEN
137 temp := REGEXP_REPLACE( temp, E'III', '' );
139 IF temp like '%II%' THEN
141 temp := REGEXP_REPLACE( temp, E'II', '' );
143 IF temp like '%IV%' THEN
145 temp := REGEXP_REPLACE( temp, E'IV', '' );
148 temp := REGEXP_REPLACE( temp, '\(\)', '');
149 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
150 family_name := REGEXP_REPLACE( family_name, ',', '' );
151 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
152 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
153 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
154 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
156 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
158 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
160 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
162 full_name TEXT := $1;
164 family_name TEXT := '';
165 first_given_name TEXT := '';
166 second_given_name TEXT := '';
170 temp := BTRIM(full_name);
171 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
172 --IF temp ~ '^\S{2,}\.' THEN
173 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
174 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
176 --IF temp ~ '\S{2,}\.$' THEN
177 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
178 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
180 IF temp ilike '%MR.%' THEN
182 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
184 IF temp ilike '%MRS.%' THEN
186 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
188 IF temp ilike '%MS.%' THEN
190 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
192 IF temp ilike '%DR.%' THEN
194 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
196 IF temp ilike '%JR.%' THEN
198 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
200 IF temp ilike '%JR,%' THEN
202 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
204 IF temp ilike '%SR.%' THEN
206 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
208 IF temp ilike '%SR,%' THEN
210 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
212 IF temp like '%III%' THEN
214 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
216 IF temp like '%II%' THEN
218 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
222 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
223 temp := BTRIM(REPLACE( temp, family_name, '' ));
224 family_name := REPLACE( family_name, ',', '' );
226 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
227 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
229 first_given_name := temp;
230 second_given_name := '';
233 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
234 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
235 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
236 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
238 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
239 second_given_name := temp;
240 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
244 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
246 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
248 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
250 full_name TEXT := $1;
252 family_name TEXT := '';
253 first_given_name TEXT := '';
254 second_given_name TEXT := '';
258 temp := BTRIM(full_name);
259 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
260 --IF temp ~ '^\S{2,}\.' THEN
261 -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
262 -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
264 --IF temp ~ '\S{2,}\.$' THEN
265 -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
266 -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
268 IF temp ilike '%MR.%' THEN
270 temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
272 IF temp ilike '%MRS.%' THEN
274 temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
276 IF temp ilike '%MS.%' THEN
278 temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
280 IF temp ilike '%DR.%' THEN
282 temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
284 IF temp ilike '%JR.%' THEN
286 temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
288 IF temp ilike '%JR,%' THEN
290 temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
292 IF temp ilike '%SR.%' THEN
294 temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
296 IF temp ilike '%SR,%' THEN
298 temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
300 IF temp like '%III%' THEN
302 temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
304 IF temp like '%II%' THEN
306 temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
310 family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
311 temp := BTRIM(REPLACE( temp, family_name, '' ));
312 family_name := REPLACE( family_name, ',', '' );
314 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
315 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
317 first_given_name := temp;
318 second_given_name := '';
321 IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
322 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
323 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
324 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
326 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
327 second_given_name := temp;
328 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
332 family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
333 first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
334 second_given_name := BTRIM(REPLACE(REPLACE(second_given_name,',',''),'"',''));
336 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
338 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;