Apex Code Development You need to sign in to do that
Don't have an account?
NMISHRA Finding a contact by phone number: SOQL or SOSL?
Hi,
Uisnh zksClient, I am trying find a contact by phone number irrespective of how the phone number is recorded in SalesForce.com, w/o punctuations, long distance codes and others. When using the following sample SOQL query to find a contact by phone number (xxx) yyy-zzz (the way it is received from phone system):
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, in case the phone number is not stored in correct format, the contact is not found even though the phone number is basically 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
How to best find contact by phone number(preferably via a single query) while ignoring the punctuations and other special characters?
Thanks again and regards
Some suggestions, corrections, and thoughts:
1. You can put wildcards in SOSL. You need to use * in the string, not %. So your search string would be '*312*' for example. http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_sosl_find.htm
2. Either SOQL or SOSL will meet your needs.
3. SOSL is great if you want to search across objects global in your org. For example, you may have phone number on Account, Contact, and say Lead. It wil allow you to execute one search across multiple objects.
4. SOQL is great if you have a very exact query you want execute against. It allows you to do things like Relationship Queries, Aggregate Results, and other stuff.
5. If you are having issues with the formatting of your phone numbers you can look at removing those formatting characters from the input search string prior to the SOQL. Something like queryString.replaceAll('(',''). I'm freehanding this so its something to consider. I don't remember off hand how the Phone fields is stored at DB level.
Thanks. I'm trying to figure how to use a wild card in the beginning of a search term: something like *xxxyyyzzz to find phone numbers like +1 (xxx) yyy-zzz and others. It doesn't seem to work in SOSL and also not in the Advanced Search in SalesForce.com search.
Thanks again and regards
If you format the phone number that you want to search like this %xxx%yyy%zzz. Would find all the cases you want. Of couse this search is terrible expensibe.
phone.replaceAll('[^0-9]','');So you can't simply match an all-numeric phone number against whatever string is stored in the DB. I guess wildcards will help if you can assume it is a US-formatted phone.