Wednesday, March 9, 2011

Remove Leading Zeros From a String

I get a lot of email from people wanting to find/replace specific patterns in string fields, so I'm writing this post to *briefly* introduce regular expressions (commonly called regex), and answer a question that was posted by Chris earlier today on the Batch Edit String Fields post.

The term "regular expression" is used to describe a flexible method for searching for patterns within a string. Regular expressions use special characters to match any number and/or combination of characters. You can read a little more about them on Wikipedia here:

I'll explain the concept further and give some examples in my next post, but for now I want to answer the question that was posted by Chris. Chris needs to trim leading zeros from a string. I created a .cal file (for both Python and VB Script) that uses a regex and can be loaded into the Field Calculator to run on any string field. The regex I used for this is "^0+", where ^ denotes the beginning of the string to match, and + matches the preceding character (in this case 0) one or more times. I use a replace function to replace this pattern with an empty string, to completely remove all zeros at the beginning of the string.

The .cal files can be downloaded from the Geospatial Database:

*Note: The .cal file for Python can only be used in ArcGIS 10.x. If you are using any earlier ArcGIS version, you will need to use the .cal file for VB Script (

How to use:
Open the Field Calculator either through the Geoprocessing tool in Data Management Tools > Fields > Calculate Field or by right clicking the field you want to edit in the attribute table and selecting Field Calculator... Select the Load... button and browse to the .cal file. If you use the VB Script .cal file, change the field within the Replace function in the Code Block to the name of the field containing the string you want to search on, leaving the field name in brackets. If you are using the Python .cal file (in ArcGIS 10.x only), be sure to change the Parser option at the top of the dialog to Python and change the name of the field in the Expression to the name of your field, leaving the field name within the exclamation points. When you click OK, the field will update and remove all leading zeros within each record.

More on regex later. Stay tuned!