3

I would like to write a query which searches based on a telephone number through SOAP API. In the clients SOQL DB the number could contain other string characters other than numbers such as: (028)9067333 or (028)-9067-333

My end query idealy would search for: WHERE Phone = '0289067333' .... without any of the non-alphnumeric chars.

I would normally use a custom function or stored procedure, but this is not possible i think?? I also tried the REMOVE(originalstring,replacethis,withthis) function eg.:

SELECT Phone FROM Contact WHERE REPLACE((Phone), ('-'), ('')) = '315961500'

, however when I pass this as a query string through the SOAP api I receive an error saying MALFORMED QUERY "expecting a right parentheses at '-'. Has anyone any solutions to my problem?

Much appreciated

  • Why you need REPLACE?(I don't know whether this is working or not) Can't you just have Phone = 315961500? – highfive Oct 28 '14 at 9:39
  • No, basically "Phone" in the database will have a value of '(315)-961-500' - I need to find that record by searching for '315961500'. In SQL server this works great but not in SOQL it seems :( – Shanyman Oct 28 '14 at 9:46
8

Unfortunately you cannot do this purely using SOQL.

If you want to stick to SOQL, you can do this one of two ways, both involve adding a new field to the object:

  • A formula field, using a number of SUBSTITUTE functions to strip out the characters you don't want.
  • A text field and an Apex trigger which uses replaceAll('[^0-9]', '') to remove all the non-numeric characters from your Phone field and save the result into the new field.

Once you've done either of those you should be able to filter on your new field within your SOQL query.

You can also use SOSL to search for results which match your phone number.

FIND {315961500} IN PHONE FIELDS RETURNING Contact(Name)

The standard SOSL behaviour will ignore your special characters, however this will also match numbers which include the number your are searching for.

Trying to get around this by switching to a SOSL exact match (surrounding your search term with ") will result in the same behaviour as your current SOSL query, and so is no good.

|improve this answer|||||
  • Thanks for the great answer Alex. This will be a generic application which needs to work on multiple company's SalesForce and so I think the SOSL solution is the only way to go??? (dont want to make changes to their existing schema). Would my code look like... – Shanyman Oct 28 '14 at 10:54
  • 'code' searchRequest objSearchReq = new searchRequest(); objSearchReq.searchString = "FIND {999999999} IN Phone FIELDS RETURNING Contact(LastName)"; objSearchReq.SessionHeader = header; objSearchReq.PackageVersionHeader = null; searchResponse objSearchResponse = queryClient.search(objSearchReq); SearchResult sr = objSearchResponse.result; } catch (Exception ex) { } – Shanyman Oct 28 '14 at 10:55
0

Adding an extra field that just holds the phone numbers, stripped of all formatting, is one approach. Although it's easier to do this using a Formula field, those fields are not normally indexed (although you could request a custom index from Salesforce). The better approach is probably to write a trigger. Short of adding an extra field the best solution I've found for this is to use a SOSL query since then you can specify "Find {33333333} in Phone Fields" to search all of the phone fields on a record. For my purposes I've found it helpful to first strip out all the special characters from a queried phone number and then to search for one or more formatted variations:

// Assuming you have your query phone number as a String named "phone"
String phoneDigits = phone.replaceAll('[^0-9?*]','');
phoneSearchTerm = phoneDigits;
if(phoneDigits.length()>6)  {
   phoneSearchTerm += ' OR ' + 
     String.format('\\({0}\\) {1}\\-{2}', new String[]{phoneDigits.substring(0,3), phoneDigits.substring(3, 6), phoneDigits.substring(6)});
}
query = 'FIND {' + phoneSearchTerm + '} IN phone fields returning account(AccountNumber)';
|improve this answer|||||

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.