my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
return $barcode . $checkdigit;
$$ LANGUAGE PLPERL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
+ DECLARE
+ phone TEXT := $1;
+ areacode TEXT := $2;
+ temp TEXT := '';
+ output TEXT := '';
+ n_digits INTEGER := 0;
+ BEGIN
+ temp := phone;
+ temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
+ n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
+ IF n_digits = 7 THEN
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
+ output := (areacode || '-' || temp);
+ ELSE
+ output := temp;
+ END IF;
+ RETURN output;
+ END;
+
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;