"Where" clause which removes alphanumeric characters?

Question

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

Answer 1

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.

Answer 2

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)';