jandersen316

Need to locate state abbreviations within a text field

Discussion created by jandersen316 on Jun 10, 2016
Latest reply on Jun 10, 2016 by erolst

I'm trying to parse a notes field from another database that has billing address info in random locations within the notes. The end goal to extract the entire mailing address as separate fields (Street, City, State, Zip) from the original notes. The state abbreviation in the original notes field is always two letters, always in CAPS, and is always followed by the zip code. But I can't seem to do this using PatternCount. I need to find only the two-letter combinations that are capitalized,, but PatternCount won't get that specific. Example:

 

In the following text string I want to isolate 'SC' (the abbreviation for South Carolina). So I've created a long calculation that looks for every two-letter state abbreviation. The hang-up is that 'Mt' (re: Mt Pleasant), is also a state abbreviation (for Montana), so when I perform the calculation on the following text string:

 

1044 Johnnie Dodds Blvd #L3

Mt Pleasant SC 29464

 

Filemaker returns 'Mt'. I need a calculation that finds only the two-letter upper case string for each state abbreviation, and PatternCount alone won't do it. I've tried using the == modifier within the calculation, but it seems that Filemaker wants to find a literal "==SC". I've also tried using the Upper() function...so far no success. Any ideas?

 

Here are a few of the 50+ lines of code I'm using (unsuccessfully) to find the state (and Canadian province) abbreviations:

 

Case(

 

PatternCount(OldDBNotes; Upper("MT ")) = 1; Position(OldDBNotes; Upper("MT "); 1;1);

PatternCount(OldDBNotes; Upper("SC ")) = 1; Position(OldDBNotes; Upper("SC "); 1;1);

PatternCount(OldDBNotes; Upper("WA ")) = 1; Position(OldDBNotes; Upper("WA "); 1;1);           

 

...etc., etc., for 50+ 2-letter abbreviations.

 

I still get 'Mt' returned on my sample address, with or without the Upper() qualifier.

 

(Yes, I know this calc will return a numerical position and not the actual state abbreviation characters - but that's OK. Once I have the position, extracting the two-letter abbreviation itself is cake).

 

Any ideas? I'd be much obliged.

Outcomes