-1

I don't know if there is a way to solve this, I couldn't find it.

I am importing a CSV file where I am to create an sObject for each line. The thing is, I have to do a query using a value present in the csv file, for example let's say value = 2554543. So, the query could be something like [SELECT Id FROM sObject WHERE Custom_Field__c = value]. BUT, the problem is , the values on that Custom_Field__c could be like this 2554543 or like this 2-5545-43.

How can I query against that field? Is it possible to only compare the numbers and ignore the other characters? (I don't want to do WHERE Custom_Field__c = '2554543' OR '2-5545-43')

1 Answer 1

2

I had a similar situation where we need to query for phone numbers, but the phone number field can vary greatly in how people choose to enter them. (321)555-1212 vs 321-555-1212 etc I solved for that by creating a new field which I called "Phone Key" and in a trigger I strip all non-numeric characters and just store the 10-digit phone number 3215551212 which I then use for all queries.

obj.Phone_Key__c = ( String.isBlank(obj.Phone) ? null : obj.Phone.replaceAll('[^0-9]','') );
2
  • I understand what you did. This means Salesforce has no function to format right-hand side values on queries? I have no other choice than to create another field on my sObject (could be formula field or your approach) ? Jun 13 at 17:45
  • @joacoiudica Correct - there is no way to do what you need directly in SOQL.
    – Paul N
    Jun 13 at 17:46

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.