Regular Expressions for Shipment Tracking Code Verification

I set up a tracking page recently for orders and realized that since we ship via 3 carriers (UPS, UPSMI, and USPS) I needed to sort out which API to call based on the tracking number (I didn’t want to force the user to remember the tracking method themselves when I could figure it out for them). Using some tracking number regular expressions I found at StackOverflow I compiled the following list. I’ll add more as I find them. I’ve tested all of them and they all seem to work for what I’ve needed.

UPS (1Z numbers)
\b(1Z ?[0-9A-Z]{3} ?[0-9A-Z]{3} ?[0-9A-Z]{2} ?[0-9A-Z]{4} ?[0-9A-Z]{3} ?[0-9A-Z]|[\dT]\d\d\d ?\d\d\d\d ?\d\d\d)\b

UPS Mail Innovations (UPSMI)
MI[0-9]{6}(ABC[0-9]{7}|XYZ[0-9]{4})

USPS (4 Types)
(\b\d{30}\b)|(\b91\d+\b)|(\b\d{20}\b)
^E\D{1}\d{9}\D{2}$|^9\d{15,21}$
^91[0-9]+$
^[A-Za-z]{2}[0-9]+US$

Notes:

  1. UPSMI is a bit strange in the way tracking numbers are set up. The tracking number starts with “MI” followed by your 6 character numeric UPSMI account number (this is different than your UPS account number which normally ends with “TT”). After that, it’s up to you to determine your format for your tracking number. We’re using one of two 3-character alphabetical codes followed by either a 4 or 7 character numeric code (that’s the “(ABC[0-9]{7}|XYZ[0-9]{4})” you’ll see in the regex). While the “MI[0-9]{6}” part is standard, I recommend writing and testing your own suffix with RegExr.

UPS Mail Innovations Sorting Facilities Codes

UPS Mail Innovations (UPSMI) is a partnership between UPS and the USPS where UPS picks up from the shipper, sorts the packages at one of a number of sorting centers throughout the US, ships them to another sorting center closest to the package’s destination, then hands it off to the USPS at one of USPS’ BMC (Bulk Mail Centers, now called NDCs or Network Distribution Centers) or SCF (Sectional Center Facilities). USPS then handles delivery from there. The advantage is that for mid-sized to larger shippers, rates are equal to USPS Media Mail but delivery times are equal to First Class plus one day. I’ll likely write more about UPSMI integration with WorldShip and other applications later.

I recently set up a connection to UPS’ tracking API which works for UPSMI packages as well now but ran into an issue with the information being returned. For standard small-package tracking, UPS’ tracking API returns the city and state for each activity (pickup, sorting, destination, etc.) but for UPSMI I was simply receiving facility codes instead of city and state. It took a while to track down packages which went through each of UPSMI’s facilities, but I eventually did and managed to put together the following PHP array which can be used to convert the facility code to the correct city and state.

USPS BMCs and SCFs don’t return the facility code, so you’re stuck either simply saying “Bulk Mail Center” or “Sectional Center Facility”, or setting up another API to get tracking information from the USPS once the package leaves the UPSMI system.

    $miFacilities = Array(
        "BMC" => "Bulk Mail Center",
        "SCF" => "Sectional Center Facility",
        "OHGRV" => "Urbancrest, OH",
        "GATLA" => "Atlanta, GA",
        "TNLVR" => "La Vergne, TN",
        "CAFNN" => "Fontana, CA",
        "NJLOG" => "Logan Township, NJ",
        "WAABU" => "Auburn, WA",
        "MNMEN" => "Mendota Heights, MN",
        "ILCST" => "Carol Stream, IL",
        "UTWVY" => "West Valley City, UT",
        "TXOLL" => "Coppell, TX",
        "NCDHM" => "Durham, NC",
        "CALEA" => "San Leandro, CA",
        "MOKCY" => "Kansas City, MO",
        "AZTOL" => "Tolleson, AZ",
        "CTWDS" => "Windsor, CT",
        "FLORO" => "Orlando, FL",
        "NYEDG" => "Edgewood, NY"
        );

I hope it saves someone else out there some time!

RegExr

A Useful Tool for Testing Regular Expressions

Regular expressions are exceedingly useful, but also exceedingly painful to write. For me, at least. The variety of matching options and their myriad combinations makes it difficult to remember what does what and trial-and-error can be difficult. That’s where RegExr, a powerful tool built by gskinner.com comes in.

gskinner.com's RegExr

gskinner.com’s RegExr

To use RegExr enter your regular expression in the field at the top and write some text which contains information you’re trying to match in the large field. If your regular expression matches text, it’ll be highlighted. You can also hover your mouse of your regular expression to see a description of what that character or set does. Additionally, you can use the provided samples on the right to quickly and easily build your regex.

In my case I was looking to identify various shipping carriers from their tracking numbers. On an ecommerce site I manage we’ve developed a tracking page where the user can simply enter the tracking number we sent them and track their package. The complication here is that we ship via USPS, UPS, and UPS Mail Innovations, all of which use varied tracking codes. In RegExr I wrote a list of various valid and invalid tracking numbers from each of the carriers and tested regular expressions to ensure they matched the correct tracking numbers (and only the correct tracking numbers). In a future post I’ll post the expressions I found or wrote for this purpose.

 

MySQL's Replace Function

Replacing Unwanted Characters in MySQL Field

I recently ran into an issue where and automated import wasn’t clearing newline characters before inserting entries into my database. This resulted in thousands of entries in the database containing unwanted newlines in certain fields. To resolve this issue I could have looped through all the entries with a PHP script, but this was a one-time fix (I corrected the issue in the import so it won’t happen again) and I didn’t want to write what would be a resource-consuming looping script in PHP.

What’s the solution, then? Using MySQL’s built-in REPLACE() function. It works like this:

UPDATE table SET field = REPLACE(field, "\n", "") WHERE id > "10201";

Simple but effective. The REPLACE() function takes three parameters:

  1. The field to be searched.
  2. The string to be replaced.
  3. The replacement string.

In my case, I’m searching for the string “\n” in the field “field” and I’m replacing it with “” (nothing). I’m also restricting the replacements to only those rows with an id greater than 10201 since that’s where the import began to experience it’s issue. Why make MySQL do more work than it has to?

So, that’s the solution to this issue.

An Example of Zebra Striping in Excel

Using Conditional Formatting for Zebra Striping in Microsoft Excel

Zebra Striping in Excel is a useful way to make it easier to differentiate between long (page-wide) rows of data especially when each column stores data which looks very similar. Simply put, you use alternating colors to define rows. I’ve found this to be most useful when printing picklists or other lists for use in our warehouse.

Achieving this effect is simple in Microsoft Excel with Excel’s Conditional Formatting tool. In any version of Office after 2003, click on “Conditional Formatting” in the “Home” ribbon and create a new rule. Here the instructions diverge slightly: In Office:Mac choose “Classic” style then “Use a formula to determine which cells to format”, in Office for Windows choose the “Use a formula to determine which cells to format” Rule Type.

At this point the instructions converge again: type in the following formula into the formula box:

=MOD(ROW(),2)

After this, in Office for Windows click on “Format…” to define the format you want to use for every other row. In Office:Mac click on the “Format With” drop down and choose “Custom Format” to define your format.

The formula above uses Modular Arithmetic (the MOD() function) with the row number (the ROW() function gets the current row) as the input and 2 as the divisor. If the result is greater than 0 (any uneven row since uneven numbers can’t divide cleanly by two) the row will be formatted with your defined format, otherwise the result is 0 and the row is left with its current format.

 

Restarting a MySQL Server from the Command Line

While it doesn’t come up frequently, every once in a while you’ll need to restart a MySQL server from the command line. On my current webserver we’re running a VPS with WHM. What this means is that accessing the MySQL daemon is not as simple as using “mysqld” from the shell. So here’s how to get it to work, based on information found at this blog post, and my own experience:

#/etc/init.d/mysql start
#/etc/init.d/mysql stop
#/etc/init.d/mysql restart

That will start, stop, and restart, respectively, the MySQL server on our WHM server.

Stripping Everything but Numbers in PHP using RegEx

Here’s a useful little regular expression that will strip everything but numbers from a string in PHP.

preg_replace("/[^0-9]/","","604-619-5135");

This uses PHP’s preg_replace function to replace everything but numbers with nothing. I used it to strip the dashes from ZIP+4 codes and also reformat phone numbers without forcing the user to enter it in my preferred format (if possible, it’s always better to clean up user input yourself than yell at the user for not entering it your way).

I gleaned this solution from “Chris” on StackOverflow so all credit goes to him.