CODEWITHSUNDEEP
sqlpostgresql
swathi
swathi

Reputation: 417

Clean phone numbers which has special characters and alphabets

I have junk in the phone number field, and I want to clean them. I would like to know how to query to check if there are any special or alphabetic characters in the phone number field. Can anyone help please? I tried this query and didn't work. I need the code in PostgreSQL

select phone from table where phone like '[^[:alpha:]]' and phone <>''
-- and phone not like '[^0-9]'
order by phone

input value in the table looks like:

Phone
-----
(443)-554-6677
111-111-1111
345-rty-34fr
4345434444 ext

Output(should look like this valid phone numbers)

(443)-554-6677
111-111-1111

Your help is appreciated.

Thank You, Swathi.

Upvotes: 1

Views: 1275

Answers (2)

David Strom
David Strom

Reputation: 1

I have used this script to format US phone numbers into international format. It works really well. This script looks to see if the number is intentionally formatted and if yes, removes all useless characters I have found that if a number is ever stored with +######### formatting then it is a good number 99 percent of the time. And if it is stored with (###)###-#### formatting then it is a US Canada UK number 99 percent of the time. And that 99 percent of statistics are made up on the spot.

One downside is that this script does nothing to slice out extensions, you can do that by checking to see if the pclean output starts with +1 and is greater than 12 characters long. If yes then slice(12,-1) or something like that.

function formatPhoneNumber(phone) {
    input = phone
    if (input==null){input = "3456"}
    pclean = input.replace(/[^0-9\+]/g, "") //works
    clean = pclean.replace(/^0*/g, "")
    clean = clean.replace(/^1*/g, "")
    if (pclean.charAt(0) != "+" ) {pclean = "+1" + clean    }

    if(pclean.length < 12){pclean = null}
    return pclean 
}

Upvotes: 0

bgoldst
bgoldst

Reputation: 35314

We can use POSIX regular expressions to get the required output:

select phone from t1 where phone<>'' and phone!~'[[:alpha:]]';

output

You seem to be trying to use regular expression syntax with the like operator, but this is not correct. The like operator is extremely limited; it basically only offers the % prefix/suffix wildcard, and the _ single-character wildcard.

If you want to be even stricter, we can do something like this:

select phone from t1 where phone~'^(\([0-9]{3}\)|[0-9]{3})-[0-9]{3}-[0-9]{4}$';

output

Test fixture

drop table if exists t1;
create table t1 (phone text);
insert into t1 (phone) values ('(443)-554-6677'), ('111-111-1111'), ('345-rty-34fr'), ('4345434444 ext');

Upvotes: 1

Related Questions