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
NMISHRANMISHRA 

Searching contact by phone number

Hi,

 

  I'm using Salesforce.com partner web service to search a contact by phone number and using SOQL in its query call. It looks like the phone number has to match exactly. For instance, the following SOQL query:

 

select FirstName, LastName, Id, phone where phone like  '%(xxx) yyy-zzzz')

 

is able to find a Contact whose phone# is exactly (xxx) yyy-zzz or something like +1(xxx) yyy-zzzz but it can't match numbers like:

 

(xxx)yyy-zzzz(with no embedded blanks)

xxx-yyy-zzzz

1xxxyyyzzzz

001xxxyyyzzzz

(xxx)  yyy - zzzz(multiple embedded blanks)

 

Is there a way to write a SOQL query so that it matches a contact's phone number regardless of the punctuation?

 

Thanks in advance and regards

Shebin-KVP Business SolnsShebin-KVP Business Solns

Hi ,

 

 With  SOQL Like operator  you can  use the following code 

 

String  Phone_Number_Input='(312)-596-1000';
String xxx=Phone_Number_Input.split('-')[0];
String yyy=Phone_Number_Input.split('-')[1];
String zzz =Phone_Number_Input.split('-')[2];
String   Phone_number = '%'+ xxx+'%'+yyy+'%'+zzz+'%';

Contact cc= [select FirstName, LastName, Id, phone from Contact   where Phone like  : Phone_number ];

 

  Get the input   string  from user , in  '(xxx)-yyy-zzz'  format    then split it using  String class split method then  make the result string  Phone_number  as mentioned above then execute the query it will be matching the criteria..

 

Hope you understood, feel free for further clarification.
NMISHRANMISHRA

Thanks. Can such a thing be done in the Apex code only or not when building such a SOQL string directly when using the partner web service?

 

Thanks again and regards

 

 
Shebin-KVP Business SolnsShebin-KVP Business Solns

Its possible for webservice client programs also please try
NMISHRANMISHRA

Thanks. I get syntax error in SOQL for some reason if I use the same code. I basically want to find a contact by phone number irrespective of how the phone number is recorded in SalesForce.com, w/o punctuations, long distance codes. So, as cited earlier, when using the following sample SOQL query to find a contact by phone number (xxx) yyy-zzz:

 

select FirstName, LastName, Id, phone where phone like  '%(xxx) yyy-zzzz''

 

I'm able to find a Contact whose phone# is exactly (xxx) yyy-zzz or something like +1(xxx) yyy-zzzz but it can't match numbers like:

 

(xxx)yyy-zzzz(with no embedded blanks)

xxx-yyy-zzzz

1xxxyyyzzzz

001xxxyyyzzzz

(xxx)  yyy - zzzz(multiple embedded blanks)

 

So, on case the phone number is not stored in correct format, the contact is not found even though the phone number is actually the same. How to best handle that?

 

I looked into SOSL queries as well which look more robust and I can write query like: 

 

FIND {xxxyyyzzzz} IN phone fields returning contact(id, phone, mobilephone, homephone, otherphone, firstname, lastname)

 

That works well and is able to search all the phone fields but it does not seem to have option to specify wildcards like %xxxyyyzzzz. So, it is not able to find phone numbers like:

 

+1(xxx)  yyy-zzzz

+1  (xxx) yyy-zzzz

 

Thanks again and regards