Add Dashes to XXXXXXXXXX Formatted Phone Numbers

Clearinghouse Repository Polaris Repository
,
1

Add Dashes to XXXXXXXXXX Formatted Phone Numbers

We had a situation where a library had most of their phone numbers formatted as 4805551212 rather than the easier to read 480-555-1212. This query goes through your database and looks for numbers in the former format and reformats them to the latter format. It can be adjusted to suit a variety of similar issues.

SELECT
    PatronID,
    PhoneVoice1,
    -- The line below can be adjusted to find other phone number formats
    LEFT(PhoneVoice1, 3) + '-' + SUBSTRING(PhoneVoice1, 4, 3) + '-' + RIGHT(PhoneVoice1, 4) AS [NewPhoneFormat]
INTO
    Polaris.Polaris.TempPhoneUpdate
FROM
    Polaris.Polaris.PatronRegistration WITH (NOLOCK)
WHERE -- Looks for anything that's not like 480-555-1212
    PhoneVoice1 NOT LIKE '%-%-%'
AND -- Limits it to the standard ten digit phone number
    LEN(PhoneVoice1) = 10

UPDATE
    Polaris.Polaris.PatronRegistration
SET
    PatronRegistration.PhoneVoice1 = TempPhoneUpdate.NewPhoneFormat
FROM
    Polaris.Polaris.TempPhoneUpdate
WHERE
    PatronRegistration.PatronID = TempPhoneUpdate.PatronID

DROP TABLE Polaris.Polaris.TempPhoneUpdate