Finding patrons with invalid phone numbers (Polaris Find Tool SQL)

Clearinghouse Repository Polaris Repository
1

Below is a query that can be used in a Patron Find Tool SQL Search to locate patrons whose phone numbers don’t cleanly resolve to a standard 10-digit numeric value.

What it does:

  • Checks PhoneVoice1, PhoneVoice2, and PhoneVoice3 for each patron

  • Strips common formatting characters (spaces, dashes, dots, parentheses, plus signs)

  • Returns distinct PatronIDs where the cleaned phone number:

    • Is not exactly 10 digits or

    • Contains non-numeric characters

This can identify records that may need cleanup before notices, SMS integrations, or data exports.

SELECT DISTINCT
    p.PatronID
FROM Polaris.Polaris.PatronRegistration AS p
CROSS APPLY (VALUES (p.PhoneVoice1), (p.PhoneVoice2), (p.PhoneVoice3)) AS v(Phone)
CROSS APPLY (
    SELECT
        CleanPhone =
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                LTRIM(RTRIM(v.Phone)),
                '(', ''), ')', ''), '-', ''), '.', ''), ' ', ''), '+', '')
) AS c
WHERE
    v.Phone IS NOT NULL
    AND LTRIM(RTRIM(v.Phone)) <> ''
    AND (
        LEN(c.CleanPhone) <> 10
        OR c.CleanPhone LIKE '%[^0-9]%'
    )