function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DeBoLoDeBoLo 

handling different phone number formats in SOQL

Hi,

 

I am facing a problem when retrieving contact records based on phone number. I am unable to retrieve the record if it has the phone number in different format (Ex: 123-123-1234, (123)123-1234, 1231231234, 1-(123)123-1234....etc). I am using SOQL query to retrieve the data.

 

I want to retrieve records even the phone number format is different. Any solutions?

 

Thanks,

Best Answer chosen by Admin (Salesforce Developers) 
Ispita_NavatarIspita_Navatar

One of the possible solutions of your problem will be to create a formula field which stores the phone numbers after stripping special characters like (,),- etc.

Also when you try to retrieve a record you can pass the stripped phone number in the where clause and match it against the phone number field which is a formula.

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

 

All Answers

Ispita_NavatarIspita_Navatar

One of the possible solutions of your problem will be to create a formula field which stores the phone numbers after stripping special characters like (,),- etc.

Also when you try to retrieve a record you can pass the stripped phone number in the where clause and match it against the phone number field which is a formula.

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

 

This was selected as the best answer
TechTrackerTechTracker

Hi, 

 

I like the solution. Could you please provide the formula for this?

 

Thanks
DeBoLoDeBoLo

Thanks Ispita Saha. I did it. It is working fine.

 

FYI - formula for this field: SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(Phone, '.', '')  , ' ', '')  , '-', '')  , ')', '')  , '(', '')

 

Thanks
rwilcox@thinkingphones.comrwilcox@thinkingphones.com
@DeBoLo...

I am looking to do something very similar in nature, would you mind sharing your script/query?

Thanks for your time.