sql/base/04-names.sql

   1 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
   2     DECLARE
   3         full_name TEXT := $1;
   4         before_comma TEXT;
   5         family_name TEXT := '';
   6         first_given_name TEXT := '';
   7         second_given_name TEXT := '';
   8         suffix TEXT := '';
   9         prefix TEXT := '';
  10     BEGIN
  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;
  13
  14         IF suffix = before_comma THEN
  15             suffix := '';
  16         END IF;
  17
  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 );
  21
  22         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
  23     END;
  24 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
  25
  26 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
  27     DECLARE
  28         full_name TEXT := $1;
  29         temp TEXT;
  30         family_name TEXT := '';
  31         first_given_name TEXT := '';
  32         second_given_name TEXT := '';
  33         suffix TEXT := '';
  34         prefix TEXT := '';
  35     BEGIN
  36         temp := full_name;
  37         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
  38         IF temp ilike '%MR.%' THEN
  39             prefix := 'Mr.';
  40             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
  41         END IF;
  42         IF temp ilike '%MRS.%' THEN
  43             prefix := 'Mrs.';
  44             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
  45         END IF;
  46         IF temp ilike '%MS.%' THEN
  47             prefix := 'Ms.';
  48             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
  49         END IF;
  50         IF temp ilike '%DR.%' THEN
  51             prefix := 'Dr.';
  52             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
  53         END IF;
  54         IF temp ilike '%JR%' THEN
  55             suffix := 'Jr.';
  56             temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
  57         END IF;
  58         IF temp ilike '%JR,%' THEN
  59             suffix := 'Jr.';
  60             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
  61         END IF;
  62         IF temp ilike '%SR%' THEN
  63             suffix := 'Sr.';
  64             temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
  65         END IF;
  66         IF temp ilike '%SR,%' THEN
  67             suffix := 'Sr.';
  68             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
  69         END IF;
  70         IF temp ~ E'\\sII$' THEN
  71             suffix := 'II';
  72             temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
  73         END IF;
  74         IF temp ~ E'\\sIII$' THEN
  75             suffix := 'III';
  76             temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
  77         END IF;
  78         IF temp ~ E'\\sIV$' THEN
  79             suffix := 'IV';
  80             temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
  81         END IF;
  82
  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 );
  86
  87         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
  88     END;
  89 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
  90
  91 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
  92     DECLARE
  93         full_name TEXT := $1;
  94         temp TEXT;
  95         family_name TEXT := '';
  96         first_given_name TEXT := '';
  97         second_given_name TEXT := '';
  98         suffix TEXT := '';
  99         prefix TEXT := '';
 100     BEGIN
 101         temp := full_name;
 102         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
 103         IF temp ilike '%MR.%' THEN
 104             prefix := 'Mr.';
 105             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
 106         END IF;
 107         IF temp ilike '%MRS.%' THEN
 108             prefix := 'Mrs.';
 109             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
 110         END IF;
 111         IF temp ilike '%MS.%' THEN
 112             prefix := 'Ms.';
 113             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
 114         END IF;
 115         IF temp ilike '%DR.%' THEN
 116             prefix := 'Dr.';
 117             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
 118         END IF;
 119         IF temp ilike '%JR.%' THEN
 120             suffix := 'Jr.';
 121             temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
 122         END IF;
 123         IF temp ilike '%JR,%' THEN
 124             suffix := 'Jr.';
 125             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
 126         END IF;
 127         IF temp ilike '%SR.%' THEN
 128             suffix := 'Sr.';
 129             temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
 130         END IF;
 131         IF temp ilike '%SR,%' THEN
 132             suffix := 'Sr.';
 133             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
 134         END IF;
 135         IF temp like '%III%' THEN
 136             suffix := 'III';
 137             temp := REGEXP_REPLACE( temp, E'III', '' );
 138         END IF;
 139         IF temp like '%II%' THEN
 140             suffix := 'II';
 141             temp := REGEXP_REPLACE( temp, E'II', '' );
 142         END IF;
 143         IF temp like '%IV%' THEN
 144             suffix := 'IV';
 145             temp := REGEXP_REPLACE( temp, E'IV', '' );
 146         END IF;
 147
 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, ',', '' );
 155
 156         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 157     END;
 158 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 159
 160 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
 161     DECLARE
 162         full_name TEXT := $1;
 163         temp TEXT;
 164         family_name TEXT := '';
 165         first_given_name TEXT := '';
 166         second_given_name TEXT := '';
 167         suffix TEXT := '';
 168         prefix TEXT := '';
 169     BEGIN
 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'));
 175         --END IF;
 176         --IF temp ~ '\S{2,}\.$' THEN
 177         --    suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
 178         --    temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
 179         --END IF;
 180         IF temp ilike '%MR.%' THEN
 181             prefix := 'Mr.';
 182             temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
 183         END IF;
 184         IF temp ilike '%MRS.%' THEN
 185             prefix := 'Mrs.';
 186             temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
 187         END IF;
 188         IF temp ilike '%MS.%' THEN
 189             prefix := 'Ms.';
 190             temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
 191         END IF;
 192         IF temp ilike '%DR.%' THEN
 193             prefix := 'Dr.';
 194             temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
 195         END IF;
 196         IF temp ilike '%JR.%' THEN
 197             suffix := 'Jr.';
 198             temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
 199         END IF;
 200         IF temp ilike '%JR,%' THEN
 201             suffix := 'Jr.';
 202             temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
 203         END IF;
 204         IF temp ilike '%SR.%' THEN
 205             suffix := 'Sr.';
 206             temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
 207         END IF;
 208         IF temp ilike '%SR,%' THEN
 209             suffix := 'Sr.';
 210             temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
 211         END IF;
 212         IF temp like '%III%' THEN
 213             suffix := 'III';
 214             temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
 215         END IF;
 216         IF temp like '%II%' THEN
 217             suffix := 'II';
 218             temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
 219         END IF;
 220
 221         IF temp ~ ',' THEN
 222             family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
 223             temp := BTRIM(REPLACE( temp, family_name, '' ));
 224             family_name := REPLACE( family_name, ',', '' );
 225             IF temp ~ ' ' THEN
 226                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
 227                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
 228             ELSE
 229                 first_given_name := temp;
 230                 second_given_name := '';
 231             END IF;
 232         ELSE
 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') );
 237             ELSE
 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') );
 241             END IF;
 242         END IF;
 243
 244         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 245     END;
 246 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 247
 248 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (TEXT) RETURNS TEXT[] AS $$
 249     DECLARE
 250         full_name TEXT := $1;
 251         temp TEXT;
 252         family_name TEXT := '';
 253         first_given_name TEXT := '';
 254         second_given_name TEXT := '';
 255         suffix TEXT := '';
 256         prefix TEXT := '';
 257     BEGIN
 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'));
 263         --END IF;
 264         --IF temp ~ '\S{2,}\.$' THEN
 265         --    suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
 266         --    temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
 267         --END IF;
 268         IF temp ilike '%MR.%' THEN
 269             prefix := 'Mr.';
 270             temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
 271         END IF;
 272         IF temp ilike '%MRS.%' THEN
 273             prefix := 'Mrs.';
 274             temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
 275         END IF;
 276         IF temp ilike '%MS.%' THEN
 277             prefix := 'Ms.';
 278             temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
 279         END IF;
 280         IF temp ilike '%DR.%' THEN
 281             prefix := 'Dr.';
 282             temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
 283         END IF;
 284         IF temp ilike '%JR.%' THEN
 285             suffix := 'Jr.';
 286             temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
 287         END IF;
 288         IF temp ilike '%JR,%' THEN
 289             suffix := 'Jr.';
 290             temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
 291         END IF;
 292         IF temp ilike '%SR.%' THEN
 293             suffix := 'Sr.';
 294             temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
 295         END IF;
 296         IF temp ilike '%SR,%' THEN
 297             suffix := 'Sr.';
 298             temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
 299         END IF;
 300         IF temp like '%III%' THEN
 301             suffix := 'III';
 302             temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
 303         END IF;
 304         IF temp like '%II%' THEN
 305             suffix := 'II';
 306             temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
 307         END IF;
 308
 309         IF temp ~ ',' THEN
 310             family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
 311             temp := BTRIM(REPLACE( temp, family_name, '' ));
 312             family_name := REPLACE( family_name, ',', '' );
 313             IF temp ~ ' ' THEN
 314                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
 315                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
 316             ELSE
 317                 first_given_name := temp;
 318                 second_given_name := '';
 319             END IF;
 320         ELSE
 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') );
 325             ELSE
 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') );
 329             END IF;
 330         END IF;
 331
 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,',',''),'"',''));
 335
 336         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
 337     END;
 338 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 339