Example of cleaning up / normalizing opac.default_phone values

README

Normalizes values such as:

" 9999999999"
"(999) 999-9999"
"(999) 9999999"
"(999)999-9999"
"9-(999)-999-9999"
"9-999-999-9999"
"999 999 9999"
"999 999-9999"
"999 9999999 "
"999-999-9999"
"999-9999"
"999-9999999"
"999.999.9999 "
"999/999/9999"
"9999999"
"9999999999"
"99999999999"
"999_999_9999"

Does not normalize values such as the following:

"999-999-99999"
"999-9999 ZZZ 9999"
"999-99999999"
"999999"
"99999999999"

survey_data.sql

-- mask and summarize things addressed by these updates
SELECT DISTINCT REGEXP_REPLACE(REGEXP_REPLACE(aus.value, '[0-9]', '9', 'g'), '[a-zA-Z]', 'Z', 'g')
FROM actor.usr_setting aus
JOIN actor.usr au ON au.id = aus.usr
WHERE au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND (
value ~ '^" *1?\d{10} *"$' -- update 1
OR value ~ '^" *1?[-_ /.]*\(?\d{3}\)?[-_ /.]*\d{3}[-_ /.]*\d{4} *"$' -- update 2
OR value ~ '^" *\d{3}[-_ /.]*\d{4} *"$') -- update 3
ORDER BY 1;

-- mask and summarize things NOT addressed by these updates
SELECT DISTINCT REGEXP_REPLACE(REGEXP_REPLACE(aus.value, '[0-9]', '9', 'g'), '[a-zA-Z]', 'Z', 'g')
FROM actor.usr_setting aus
JOIN actor.usr au ON au.id = aus.usr
WHERE au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value <> '""'
AND value !~ '^" *1?\d{10} *"$' -- update 1
AND value !~ '^" *1?[-_ /.]*\(?\d{3}\)?[-_ /.]*\d{3}[-_ /.]*\d{4} *"$' -- update 2
AND value !~ '^" *\d{3}[-_ /.]*\d{4} *"$' -- update 3
ORDER BY 1;

updates.sql

-- Clean up messy opac.default_phone settings values
-- Standardize on "\d{3}-\d{3}-\d{4}", such as 555-555-1212


-- update 1
-- no delimiters, optional leading one, optional leading/trailing spaces
SELECT aus.value, REGEXP_REPLACE(aus.value, '^" *1?(\d{3})(\d{3})(\d{4}) *"$', '"\1-\2-\3"')
FROM actor.usr_setting aus
JOIN actor.usr au ON au.id = aus.usr
WHERE au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value ~ '^" *1?\d{10} *"$';

UPDATE actor.usr_setting aus
SET value = REGEXP_REPLACE(aus.value, '^" *1?(\d{3})(\d{3})(\d{4}) *"$', '"\1-\2-\3"')
FROM actor.usr au
WHERE au.id = aus.usr
AND au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value ~ '^" *1?\d{10} *"$';


-- update 2
-- wrong/mixed delimiters: dash, underscore, space, slash, period, optional leading one, optional leading/trailing spaces
SELECT aus.value, REGEXP_REPLACE(aus.value, '^" *1?[-_ /.]*\(?(\d{3})\)?[-_ /.]*(\d{3})[-_ /.]*(\d{4}) *"$', '"\1-\2-\3"')
FROM actor.usr_setting aus
JOIN actor.usr au ON au.id = aus.usr
WHERE au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value !~ '^"\d{3}-\d{3}-\d{4}"$'
AND value !~ '^" *1?\d{10} *"$'
AND value ~ '^" *1?[-_ /.]*\(?\d{3}\)?[-_ /.]*\d{3}[-_ /.]*\d{4} *"$';

UPDATE actor.usr_setting aus
SET value = REGEXP_REPLACE(aus.value, '^" *1?[-_ /.]*\(?(\d{3})\)?[-_ /.]*(\d{3})[-_ /.]*(\d{4}) *"$', '"\1-\2-\3"')
FROM actor.usr au
WHERE au.id = aus.usr
AND au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value !~ '^"\d{3}-\d{3}-\d{4}"$'
AND value !~ '^" *1?\d{10} *"$'
AND value ~ '^" *1?[-_ /.]*\(?\d{3}\)?[-_ /.]*\d{3}[-_ /.]*\d{4} *"$';


-- update 3
-- fill default area code of 231
SELECT aus.value, REGEXP_REPLACE(aus.value, '^" *(\d{3})[-_ /.]*(\d{4}) *"$', '"231-\1-\2"')
FROM actor.usr_setting aus
JOIN actor.usr au ON au.id = aus.usr
WHERE au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value ~ '^" *\d{3}[-_ /.]*\d{4} *"$';

UPDATE actor.usr_setting aus
SET value = REGEXP_REPLACE(aus.value, '^" *(\d{3})[-_ /.]*(\d{4}) *"$', '"231-\1-\2"')
FROM actor.usr au
WHERE au.id = aus.usr
AND au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value ~ '^" *\d{3}[-_ /.]*\d{4} *"$';


-- Display values that are not addressed by the queries above
SELECT aus.value
FROM actor.usr_setting aus
JOIN actor.usr au ON au.id = aus.usr
WHERE au.home_ou IN (SELECT id FROM actor.org_unit_descendants(1))
AND aus.name = 'opac.default_phone'
AND value <> '""'
AND value !~ '^"\d{3}-\d{3}-\d{4}"$' -- correct
AND value !~ '^" *1?\d{10} *"$' -- update 1
AND value !~ '^" *1?[-_ /.]*\(?\d{3}\)?[-_ /.]*\d{3}[-_ /.]*\d{4} *"$' -- update 2
AND value !~ '^" *\d{3}[-_ /.]*\d{4} *"$' -- update 3
ORDER BY aus.value;