# Canonicalizing a large set of addresses using many regex substitutions

I have a script that is standardizing a large amount of data in the database. The standardization involves applying over 500 regular expressions to the data.

Here is some quick pseudocode:

Load the records from the database
for each record
for each regular expression ##predefined list of 500 regular expressions
apply regular expression to record
end;
write record to file
end;


Here is the relevant block of code in Perl:

while(my @row = $queryHandle->fetchrow_array()) {$accountKey = @row[0];
$addressLine1 = @row[1];$addressLine2 = @row[2];

#first remove all special characters leaving only numbers and alphabets
$addressLine1 =~ s/[^A-Za-z0-9 ]//g;$addressLine2 =~ s/[^A-Za-z0-9 ]//g;

for my $regexRef (@regexesList) { #now standardize the addresses$addressLine1 =~ s/$regexRef->{pattern}/$regexRef->{output}/ig;
$addressLine2 =~ s/$regexRef->{pattern}/$regexRef->{output}/ig; } my$standardizedAddress = $addressLine1 .$addressLine2;
$standardizedAddress =~ s/\s+//g; #remove all white space #Et_Run_Log("the normalized address is:$normalizedAddress");

print $dataFileHandle "${standardizedAddress}\n";
$rowCount++; }  An excerpt of regexList: my %regexHash;$regexHash{pattern} = "\bstr\b";
$regexHash{output} = "street"; #add regex hash to list push(@regexesList, {%regexHash});$regexHash{pattern} = "\brd\b";
$regexHash{output} = "road"; push(@regexesList, {%regexHash});  This is working code but the performance is abysmal. Currently the script has been running for 2.5hours and has written out 3.13 million records to the output file with about 7million to go. Is this the best it can get? Is there another faster, or less slower way? Maybe writing each row to a file first and then run each regular expression on the whole file? I would like to know if there is better way to implement this before I try the above mentioned alternative. • $accountKey = @row[0] and similar lines assign a list to a scalar variable. Does this code work? – 200_success Jul 4 '14 at 1:45
• the code is working code.. its actually "crawling" in test environment right now. Also added an excerpt of the list of hashes @200_success – Mos No Jul 4 '14 at 1:56
• @200_success It works, but it's not really what OP means - it emits warning with use warnings;: Scalar value @row[0] better written as $row[0]. – Xaerxess Jul 4 '14 at 8:24 • 2.5 hour = 9000 seconds. 3.13 mil records in 9000 seconds: 347 records per second. If for each record is applied 500 regexes this mean 173_000 regexes per second. IMHO, not bad for an interpreted language... – jm666 Jul 20 '14 at 13:04 • @jm666 if you think that is not bad then you will be blown by the final performance achieved thanks to the help of the community at stackexchange and stackoverflow: 15 minutes to process all 10 million records!!! perl is a monster in text processing! – Mos No Jul 21 '14 at 16:02 ## 1 Answer Nested loops can be problematic, but you can build regex which can be applied once instead of running foreach my %replace = ( str => { re => '\bstr\b', output => "street" }, rd => { re => '\brd\b', output => "road" }, ); my ($reCombined) = map qr/($_)/, join "|", map$_->{re}, values %replace;


and later inside while loop,

# my $string = "Foo str bar rd foo";$string =~ s/$reCombined/$replace{\$1}{output}/g;

• That only works for regexes that represent one fixed string. – 200_success Jul 4 '14 at 8:56
• @200_success true, it is an assumption on what OP showed so far. – mpapec Jul 4 '14 at 9:06