In SOQL, how do I get just the left N charactes for a compare?

Question

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.

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
You might find this answer helpful: http://salesforce.stackexchange.com/questions/12522/is-there-a-way-to-do-like-against-a-list-of-values — Doug B, Jun 18 '13 at 13:35
Just think of the sweet sweet karma I have missed out on! :( — Doug B, Jun 18 '13 at 15:42

score 8 · Accepted Answer · edited Feb 27 '23 at 11:09

8

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 WHERE Name LIKE :filter];

edited Feb 27 '23 at 11:09

Sander de Jong

answered Jun 18 '13 at 13:46

Robert Harper

score 3 · Answer 2 · answered Jun 18 '13 at 13:52

3

Two options:

Use the % wildcard

Select Id, Name From zip_code__c Where Name like '84070%'

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'

answered Jun 18 '13 at 13:52

Matt K

2 Answers2