Search phone number field with different formats

[disturbedone]

Hi, a relative SQL newbie here although basic SELECT etc statements are understood.

The query I have is this....
A database with simple contact information has a field for a phone number. It's VARCHAR(25) and has had various forms for the phone number entered eg '(123) 9876543', '+61 (3) 98765432', 098765432. I have an application that supplies a variable based on the caller ID which is supplied in the form of 0398765432. What SELECT statement can I run that disregards any character that's not numeric, including spaces???

The theory is that I want to be able to find the customer irrelevant on how the phone number has been entered in the database. In my scenario I do have an application that does this but I can't get access to the code to find out how it's done and want to write a new app to do it.

Any suggestions?
Thx

 

[r937]

the number of non-numeric characters in the column should be limited to only a few -- parentheses, dash, plus sign, and space -- so you can simply nest a few REPLACE functions...

 WHERE REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(phone,'(','')
                    ,')','')
                    ,'-','')
                    ,'+','')
                    ,' ','') = '0398765432'

of course, this means a table scan, but that's the price you pay for storing dirty data, eh





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[disturbedone]

Cool. Thanks for that. I'll give it a go.

As for the dirty data, thank SugarCRM for supplying it . Their example data stores numbers in the unconventional format, but highly US, format of "(123) 456-7890".

IMO a phone number should be in the format +CCC (AA) NNNNNNNN where C=country code, A=area code, N=number. These can vary in lengths depending on country but if the number is just stored as CANNNNNNNN and somehow displayed in the client app how you prefer to see it (I assume that's possible somehow??) then all is good.