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:

http://en.wikipedia.org/wiki/Regular_expression

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:

TrimLeadingZeros_VB.cal

TrimLeadingZeros_Python.cal

*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 (TrimLeadingZeros_VB.cal).


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!

10 comments:

  1. Do you have any idea how to add leading zeros to a fixed string filed from varying integer values using python expressions in the field calculator?

    Existing inter values;
    9
    18
    145

    Desired String Values fixed to 5 characters;
    00009
    00018
    00145

    Thanks,
    Matt Eitrem
    meitrem@coawi.org

    ReplyDelete
    Replies
    1. (I know this is a year later, but in case anyone stumbles upon this...)

      In python, try the zfill() function. For example, if you were looking to fill it like a zipcode:

      >>> mystringnumber = '234'
      >>> mystringnumber.zfill(5)
      '00234'

      Delete
    2. strOut = ("00000"+str(numIn))[-5:]

      Delete
  2. Of course! Now I know how to divide by zero! -RSR

    ReplyDelete
  3. cool blog, Brooke! just wondering though...
    I have a field (in a fGDB) with the attribute:
    LUKE "SKY"WALKER

    I can seem to find any solution in ModelBuilder to remove the quotation marks. i've tried regex, chr(34), etc, etc...

    Any pointers??? Thanks! Skip

    ReplyDelete
  4. Hi Skip,

    Thanks for visiting my blog. Check out this post from a couple years ago.

    http://www.geospatialanalyst.com/2009/10/find-and-replace-for-geoprocessing.html

    It's a script for Find and Replace. You can use it to find your quotation marks. Since you actually want to remove the quotation marks and not really replace them, in the replace parameter, enter in an empty quote like this: ""

    I hope that works for you!

    Brooke

    ReplyDelete
  5. Awesome, the pyReplace script worked like a charm. You're the best, Brooke!

    Many Thanks!
    ~Skip

    ReplyDelete
  6. to remove leading zeros, in Python, consider:
    return val.lstrip("0")

    ReplyDelete
  7. Yep, that'll also work. Thanks, Martin!

    ReplyDelete
  8. Thank you! This was very helpful.

    ReplyDelete