CODEWITHSUNDEEP
sqlpostgresql
swathi
swathi

Reputation: 417

Phone number pattern to clean up

I have phone number field to clean up and trying to find the junk number which are in this format patterns of numbers: like 000000 ,111111,222222,3333333,4444444,....

Phone
----------------
(444)333-7777
555.666.6666
333.555-7777
222-222-2222
1111111111
5104554535
(555)(353)(5343)

Phone(output i want to see is)

222-222-2222
1111111111

and second question i want to know the count of the records has special characters.from the above phone field i want to get 5 as count.

Your help is appreciated.

Upvotes: 1

Views: 6861

Answers (4)

Crazyd
Crazyd

Reputation: 416

fnPhoneFix function created to fix all common phone numbers errors to and normalizes phone numbers.

Function removes all Non-Numeric characters, reduces above 10 digit numbers to 10, below 10 numbers set to '' and all repeating numbers like 2222222222 set to ''. These are the most common errors needing to fix a phone number improperly screened.

To Create the fnPhoneFix Function use the following code:

CREATE FUNCTION [dbo].[fnPhoneFix](@PhoneOld VarChar(20))
    Returns VarChar(10)
AS
    Begin
    Declare @iCnt Int = 0
    Declare @PhoneNew VarChar(15)  = ''

    IF @PhoneOld IS NULL
        RETURN NULL;

    While @iCnt <= LEN(@PhoneOld)
        Begin
            IF Substring(@PhoneOld,@iCnt,1) >= '0' AND Substring(@PhoneOld,@iCnt,1) <= '9'
            Begin
                SET @PhoneNew = @PhoneNew + Substring(@PhoneOld,@iCnt,1)
            End
            Set @iCnt = @iCnt + 1
        End

    If LEN(@PhoneNew) > 10 and Left(@PhoneNew, 1) = '1'
        Set @PhoneNew = RIGHT(@PhoneNew,10);
    Else
        Set @PhoneNew = Left(@PhoneNew,10);

    If @PhoneNew = '0000000000' or @PhoneNew = '1111111111' 
            or @PhoneNew = '2222222222' or @PhoneNew = '3333333333' or @PhoneNew = '4444444444' 
            or @PhoneNew = '5555555555' or @PhoneNew = '6666666666' or @PhoneNew = '7777777777' 
            or @PhoneNew = '8888888888' or @PhoneNew = '9999999999' or LEN(@PhoneNew) <> 10
        Set @PhoneNew ='';
    Return @PhoneNew
End

You can add formatting of @PhoneNew if you want to set numbers to a standardized Phone Format or leave as just as 10 digit numbers.

If @PhoneNew = ''
    Return Null;
Else
    Return (Left(@PhoneNew, 3) + '-' + Substring(@PhoneNew,4,3) + '-' + Right(@PhoneNew, 4));

A sample query to Update all phone numbers in a Table by using the Function. Can also be used to update a single record, but most often used to clean up a table. Then delete all Null or '' records in table and then doing a simple count query would give you a count of good phone numbers.

Print'/*Fix Phone Numbers Call*/'
Update tblTemp
    Set Phone = dbo.fnPhoneFix(tblTemp.Phone)
From tblTemp

Upvotes: 0

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

Extending on @Shayna's excellent response, you can find the first part of your question using a regex looking for repeating numbers. Change the number between the braces to check for varying numbers of repeating digits:

with
  t1(phone) as (values ('000-000-000'::text), ('(000)000-0000'), ('000-000-0000'), ('0000000001'),('0000000000'),('765-456-6656')),
  cleaned_numbers AS ( select phone, regexp_replace(Phone, '[^0-9]', '', 'g') AS numeric_phone from t1 ) 
select * 
from cleaned_numbers 
where numeric_phone ~ '([0-9])\1{8}'
order by phone

Second part of your question, again use a regex to identify phones that have anything other than a digit or space (remove the space between brackets to find also phone numbers that have spaces, or add any symbols that you would consider valid, like brackets and dashes):

with
  t1(phone) as (values ('000-000-000'::text), ('(000)000-0000'), ('000-000-0000'), ('0000000001'),('0000000000'),('765-456-6656'))
select count(*)
from t1
where phone ~ '[^0-9 ]'

Upvotes: 0

Shayna
Shayna

Reputation: 766

This works to find ten digit repeating numbers

    WITH cleaned_numbers AS (
      select regexp_replace(number, '[^0-9]', '', 'g') AS number
      from phone_numbers
    )
    select number
    from cleaned_numbers
    where number::text ~ '([0-9])(\1)(\1)(\1)(\1)(\1)(\1)(\1)(\1)(\1)'

Upvotes: 2

Ariel
Ariel

Reputation: 978

You can specify the junk phone numbers by using LIKE clause

i.e. 

WHERE phone like ('%.%')

^ This would retrieve all junk phone numbers containing "." containing any characters % before or any characters % after

Upvotes: -1

Related Questions