Find phone numbers with nonconforming format

[#1]

Hi all,

I'm trying to figure out a way to find US phone numbers that do not fit the format XXX-XXX-XXXX. I am not wanting to change the format in Qlik, but rather identify them so we can update in our database (we use Qlik off of a copy of the database). For example, some have been entered as (XXX) XXX-XXXX, others as XXX XXX-XXXX, or XXXXXXXXXX, etc.

I haven't found any existing posts with this question, forgive me if I've missed any.

Any suggestions would be most appreciated.

Thank you,

Brian

[Aurelien_Martinez]

Hi,

I think this solution works

not (len(SubField(num, '-', 1)) = 3 and len(SubField(num, '-', 2)) = 3 and len(SubField(num, '-', 3)) = 4)

[rittermd]

I don't think that there is an easy way to do this. 

The first thing that I would do is create your load script to check for certain things and only load them (your exceptions)

For example any phone numbers less than length of 12

Any phone numbers that contain '(' or ')'

Any phone numbers that do not contain a '-'

Then only bad phone numbers will be loaded into your app and you can export that list out to work on.

This will not catch every possibility of an error.  But should get most of them.

[#4 · Accepted solution]

Thank you, Aurelien. This code did almost what I needed and I was able to modify it slightly do so. Here's what ultimately did what I wanted:

if (len(SubField("Phone", '-', 1)) = 3 and len(SubField("Phone", '-', 2)) = 3 and

    len(SubField(" Phone", '-', 3)) = 4,1,0) as [homephoneformatz]

[next table]

if (not isnull("Phone") and [homephoneformatz]=0,1,0) as homephoneformat,

For some reason the "not" in front of "len" in the example code you provided wasn't working for me. In part it was the null values being identified that I didn't care about--I didn't mention that in my post. But, it seemed to be picking up valid format phone numbers too. Anyway the above combo works.

Many thanks for your help!