+ Start a Discussion

Parsing alphanumeric strings


I'm migrating a database for a Canadian group and they have a handy trigger (Access/VBA DLookup) in their Access database that finds a contact's province after insert, based on that person postal code. Postal codes in Canada are alphanumeric and the DLookup function enables a lookup within a range of alphanumeric values. For example, the postal code 'V3H 3A6' falls within the range 'V3H 2B7' and 'V3H 5S6', which represents all of the postal codes in a particular province. I have a table of values that defines the range but not each available postal code.

Is there any way to parse these alphanumeric ranges with Apex? In other words, can I find out if an alphanumeric string falls within a range of two alphanumeric strings. Can I use this existing table that defines only the ranges or do I need to build out my own new table? Or, is there a way to build in this logic into Apex to find an alphanumeric value in a range of two alphanumeric values?
Yes, if I'm understanding how the postal codes in Canada work the compareTo string function (page 216 Apex lang ref) does exactly what you need.

I discovered that I can use "greater than" and "less than" in the where clause of my SOQL evaluating an alphanumeric string, who knew.

My problem now is how to architect this trigger. I have 18,000 records in an SObject. I need a trigger to perform a join from contact records on the OtherPostalCode field. This basically amounts to one SOQL per contact that needs updating and I'll obviously run up against governor limits when I try to push in contacts in bulk.

Short of performing a SOQL for each contact, is there a more efficient way to execute this trigger? It makes me wish there was some sort of left join feature/function from a SOQL.


If all you are doing is identifying the province, you may just use the first character and this simplifies you life. Check out this page from Canada Post and this page describing the canadian postal codes.


So with that in mind, you could use this function:

"A", "Newfoundland and Labrador",
"B", "Nova Scotia",
"C", "Prince Edward Island",
"E", "New Brunswick",
"G", "Eastern Quebec",
"H", "Metropolitan Montreal",
"J", "Western Quebec",
"K", "Eastern Ontario",
"L", "Central Ontario",
"M", "Metropolitan Toronto",
"N", "Southwestern Ontario",
"P", "Northern Ontario",
"R", "Manitoba",
"S", "Saskatchewan",
"T", "Alberta",
"V", "British Columbia",
"X", "Northwest Territories and Nunavut",
"Y", "Yukon Territory",


As for the second question... Why do you want to do a trigger for the 18000 rows? I imagine you are loading this just once, but you do not need to process the 18000 rows every time...


Anyway, one suggestion would be to do something like this:


List<sObject> myList = [SELECT ...];

for (sObject item : myList) {
item.province = CASE(...);

update myList;


You are still goinf to hit some governor limits, so maybe you want to break the SOQL in pieces... so put this inside a loop and get a small number of rows each loop. 
Message Edited by andresperez on 01-21-2009 04:51 AM

Hi Andrés,


Thanks for the great idea for encoding provinces, this is a perfect solution for provinces.  I failed to mention the rest of details for simplicity sake but not only am I encoding provinces based on postal code, but I'm also encoding Federal Riding Name and Number.  the SObject (Federal_Riding__c) contains 18,000 records, each with a starting and ending postal code representing a range and federal riding information.  


The trigger will fire on contact (before insert, before update) the fetch the riding based on the contact's postal code.  The only way that I can figure to do this is to SOQL the Federal_Riding__c object for each contact insert/update in which postal code has changed (SOQL in the for loop). I can't see a more efficient way but if you have any insight, please let me know. 



You were on the right track on a previous post...




for (sObject sObj : Trigger.new) {

List<LookupTable> ridingList = [SELECT *

FROM LookupTable

WHERE MinCode <= :sObj.PostalCode

AND MaxCode <= :sObj.PostalCode];

if (ridingList.size() == 1) {


} else {





Seems like this is how I have to structure it but I'll run into problems with a batch of contacts larger than 10-12 or so, since the SOQL limit is 20 and I have a few other SOQL firing in other code.


Thanks again for your feedback though, it's been super helpful to talk through this. 

Message Edited by DManelski on 01-21-2009 02:48 PM