suffix := 'Jr.';
temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
END IF;
+ IF temp ilike '%JR,%' THEN
+ suffix := 'Jr.';
+ temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
+ END IF;
IF temp ilike '%SR%' THEN
suffix := 'Sr.';
temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
END IF;
+ IF temp ilike '%SR,%' THEN
+ suffix := 'Sr.';
+ temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
+ END IF;
IF temp ~ E'\\sII$' THEN
suffix := 'II';
temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
suffix := 'III';
temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
END IF;
+ IF temp ~ E'\\sIV$' THEN
+ suffix := 'IV';
+ temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
+ END IF;
family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
END;
$$ LANGUAGE PLPGSQL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
+ DECLARE
+ attempt_value ALIAS FOR $1;
+ fail_value ALIAS FOR $2;
+ output NUMERIC(6,2);
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(6,2) AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ EXCEPTION
+ WHEN OTHERS THEN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+