6

In SQL, I could to something like:

SELECT Id, Name, PostalCode FROM Account WHERE LEFT( PostalCode, 5 ) IN ('84117','84070'...)

How do I do something similar in SOQL? I see date functions and aggregate functions in the SOQL documentation but nothing on others.

I've tried the following but the query errors:

select Id, name from zip_code__c where name.left(5)  = '84070'

If someone can help with this, it sure would help.

Thanks in advance.

  • 2
    What about something like WHERE PostalCode like '84117%'? – Mike Chale Jun 18 '13 at 13:16
  • Or use a Formula Field / External Id Text Field populated by Workflow Field update for the comparison. – techtrekker Jun 18 '13 at 13:20
  • Mike, the problem is that I have to compare the left 5 characters of a zip code against a set. The wild card does not work with Name in ('84070%', '84117%' ). – Robert Harper Jun 18 '13 at 13:27
  • techtrekker, That would probably work but the field count is a premium here and I'd prefer to not have to burn a field for this. – Robert Harper Jun 18 '13 at 13:30
  • 4
    You might find this answer helpful: salesforce.stackexchange.com/questions/12522/… – Doug B Jun 18 '13 at 13:35
7

Doug B's response has the answer. I'll show a simple snippet so you don't have to follow the link if you don't want to.

String[] filter = new String[]{'84117%', '84070%'};
Zip_Code__c[] zipList = [SELECT Id, Name FROM Zip_Code__c WEHRE Name LIKE :filter];
  • Glad that helped, it's a neat trick to know. – Doug B Jun 18 '13 at 13:47
2

Two options:

  1. Use the % wildcard

    Select Id, Name From zip_code__c Where Name like '84070%'
    
  2. Create a formula field

    Name_First_Five__c Formula Field:

    LEFT(Name, 5)
    

    Query:

    Select Id, Name From zip_code__c Where Name_First_Five__c = '84070'
    

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.