1

I have the requirement where I need to search a phone number in the UI like => 8405678978. So if I am passing the number in the UI like this 8405678978, it is not querying from the database as in the database it will be stored in the following format ==> (840) 567-8978 or 840-567-8978.

Below is the way I have tried in the dynamic query.

if (searchstring != null) {
    String tmpsearchstring = searchstring+'%';
    String tmpPhoneString='%'+searchstring+'%'; 
    System.debug(tmpPhoneString);
    queryString += ' AND (FirstName like :tmpsearchstring OR LastName like :tmpsearchstring OR Name like :tmpsearchstring OR EMail like :tmpsearchstring OR Phone like :tmpPhoneString)';

The problem I am facing is if in the UI I pass 840 (only 3 digits) then all other phone numbers with 840 as there middle digits or last digits are showing up....and if I pass a phone number as 8405 (4 digits) then record is not displaying because of the format of phone number stored in database. How can I get the records even if I pass 4 digits or 5 digits in the search box?

3

You can create a custom text field for each standard phone field you want to search, then create a process builder or a workflow on each standard field to strip out parentheses, dashes, and spaces and paste the stripped number into the custom field.

A quick Google search turned up this formula in the Salesforce Success community. You can modify it for use in your workflow or Process Builder:

SUBSTITUTE( 
    SUBSTITUTE( 
        SUBSTITUTE( 
            SUBSTITUTE(
                Phone, "(", ""
            ), ")", ""
        ), " ", ""
    ), "-", ""
)
  • 2
    You might want to also substitute the . character, as some people might input in the format ###.###.#### (or other similar formats depending on nationality). – Adrian Larson Aug 22 '17 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.