MapInfo Pro and resolving the issue of missing leading zeros for imported Data such as ZIPCODES

Product Affected: MapInfo Pro™
When a table is opened in MapInfo Pro that contains a character field that starts with the number zero (0), the beginning zeros will be eliminated.  This can cause problems when trying to geocode or join tables using query>sql select because of the missing zeros at the front of the number.

For example:
The two tables have postal/ZIPCODE and FIPS code columns that are missing zeros (0) from the front of the value listed.


browsers with missing zero

1. Go to Table>Maintenance>Table>Modify Structure and select a table to be corrected.
In this case, the ZIP field was incorrectly created as a Float field, which is a numeric column type.

wrong type

Select the field to be changed and at the bottom of the dialog, change the Type to Character and set the width to 5 characters.

User-added image
Click OK out of the dialog.

2.  Go to Table>Edit>Update Column.  Select the correct table and field to be modified.
Then click the Assist button.
User-added image

3.  From the Functions drop down, select Right$() and fill out the values within the parenthesis as shown.

User-added image

Click OK

User-added image

Click OK again to browse the results.

4.  When completed, go to Table>Content>Save Table or Home>File>Save>Save Table to commit the updates to the table's column.
The image shows the results after correction for each of the examples.
User-added image
If the text string contained more than 5 characters, then adjust the update value by adding more zeros in the Update Column>Assist dialog:
Right$("000000000" + Postal, 9)
Do the same for any character field of any length that is missing one or more zeros from the front of the value.


Another way to approach this issue is to use the "Format" function on the data once open in MapInfo Pro.

In MapInfo Pro, the Format$() function can be used which can format values in numerous ways.

For this example, this expression can be used to make sure the ZIPCODE  is 5 digits with zeros at the beginning:

Format$(ZIP, "00000")

Note that the input value, the ZIPCODE, must be a numeric value, such as integer, decimal or float.
If the data is a string, (i.e. Character format), the data can be forced into a numeric value using the Val() function:

Format$(Val(ZIP), "00000")

To update an existing ZIPCODE column with zeros padded, the Update Column statement or the Update Column dialog can be used:

Update ADDRESSES Set ZIP = Format$(Val(ZIP), "00000")

User-added image

For a solution outside of MapInfo Pro, this site has a solution using Excel before importing data with leading zeros such as ZIPCODE data, FIPS Data, etc.


 
UPDATED:  December 3, 2019