+ Start a Discussion
fiachra shanahan 1fiachra shanahan 1 

"Where" clause which removes alphanumeric characters?

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:

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, however when I pass this as a query string through the SOAP api I receive an error saying "expecting a right parentheses at '-'. Has anyone any solutions to my problem?

Much appreciated
You can create a formula field like below. This would give you the phone number without non-alphanumeric character. You can then query against this formula field (WHERE Phone_Formula__c = '0289067333)

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone , "(", ""), ")", ""), " ", ""), "-", ""),".", "")