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

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.



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.



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.


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.


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



Click OK



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.

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")



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.