VERIFIED SOLUTION i
X

How to add a zero to the beginning of a number in a character column in MapInfo Professional

UPDATED: April 21, 2017


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/zip code 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 code 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.
 

Environment Details

MapInfo Pro

Downloads

  • No Downloads