function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DManelskiDManelski 

Parsing alphanumeric strings

Hello,

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?
grigri9grigri9
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.


--Greg
DManelskiDManelski
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.
andresperezandresperez

Hi,

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:

CASE(MID(PostalCode,1,1),
"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",
"Unknown")
 
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


DManelskiDManelski
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. 


andresperezandresperez
Hi,
You were on the right track on a previous post...
 
Pseudocode:
 
for (sObject sObj : Trigger.new) {
	List<LookupTable> ridingList =  [SELECT * 
					 FROM LookupTable
					 WHERE MinCode <= :sObj.PostalCode
					 AND MaxCode <= :sObj.PostalCode];
	if (ridingList.size() == 1) {
 
	} else {
		error
	} 
}


DManelskiDManelski
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