VB Script to replace strings

[DeidreA]

Hello everyone! Very new to the GIS industry, but I'm hoping to learn more down the line. Forgive me if this is a very basic question.

Right now I'm having trouble doing something I think should be very simple. I'm just to replace a string in a field with another string using the Field Calculator. However, every time I run the calculation absolutely nothing appears to happen to the records within the field in question. I am working with ~4,000 records and some of them have unnecessary leading zeroes in them. Normally I would run a calculation that would remove all the leading zeroes but certain records need it while others don't. As a result, I thought it would be easier to just entirely replace the ones that have the unnecessary leading zeroes.

Here is what I'm entering into the Field Calculator

DATE_TEXT

= Replace([DATE_TEXT], "011/12/2014","11/12/2014")

The numbers are both set to strings and I have made sure to check the VB Script parser. I've also tried it using the Python parser:

DATE_TEXT

=!DATE_TEXT!.replace("011/12/2014","11/12/2014")

But with no luck. What am I doing wrong? Is it the settings? I'm working with a standalone shape file in Arc 10.3.

[RichardFairhurst · Accepted solution]

The expression looks correct, but it will only affect a record with the exact value of "011/12/2014".  I would instead select all records that have the leading zero you don't want and then remove it using the Right() method.

For a File Geodatabase feature class the SQL expression for a Select By Attribute query to select records with an extra leading zero in the month should be:

DATE_TEXT Like '0__/%'

Assuming that selects all records with an extra zero, use this VB Script expression to calculate the field to remove the first character in the string:

Right([DATE_TEXT], len([DATE_TEXT) - 1))

[DeidreA]

Thank you for your prompt reply!

The problem is that what I'm doing doesn't even affect the one type of record I'm looking at. That's why I'm so stumped.

About your solution - I'm a little confused. Why would I use the right method? The leading zeroes are to the left of the string. Wouldn't it just be Left([DATE_TEXT], len([DATE_TEXT] - 1)?

Edit: Also, my file isn't stored in a geodatabase. It's a standalone feature class. Would that SQL expression still apply?

[ChrisDonohue__GISP]

I think Richard is recommending the Right method as the information you want to retain is all on the right and the excess to be removed is on the left.  Therefore you can use Right, specifying 10 characters, and everything more than 10 characters to left will be removed.

Chris Donohue, GISP

[RichardFairhurst]

You need to use Right, because you are keeping all of the string on the right minus one character, which would elinminate the left most character (i.e., the leading zero).  Using Left would get rid of the last character in the string (i.e., the 4 of 2014), not the first character in the string.

The SQL works for Shapefiles also.  If the SQL doesn't work copy and paste an actual value from the field in to a post.  You should also paste the DATE_TEXT actual value into your original calculation.  Replace only works if every character in the second argument string is found in first argument string.  Exact spelling is required.

[DeidreA]

The SQL expression worked out! Thank you! However, for some reason, I still can not get my field calculator to give me the results I would like. It keeps tell me that there was a failure during processing. The Geoprocessing Results window told me it was expecting an extra parenthesis, but I entered:

Right([DATE_TEXT], len([DATE_TEXT] - 1)

I ended up using the Python parser and went with:

!DATE_TEXT!.lstrip("0")

It did what I wanted it to do at any rate. Thank you for your help!

[DarrenWiens2]

If you count the number of opening (2) and closing (1) parentheses in your RIGHT statement, they are different. They should always match.

Right([DATE_TEXT], len([DATE_TEXT] - 1))​

[RichardFairhurst]

The missing parenthesis was an error on my part.  The expression should have been:

Right([DATE_TEXT], len([DATE_TEXT] - 1))

Just poor proofreading.  I fixed the calculation expression in my original post, since you marked that post as the correct answer.  That way the answer will be all correct for anyone else that sees it.

Your calculation is just as good, and there are probably several other ways to remove the leading zero once you have the correct selection of records.  The bottom line is that the pattern is fixed now.  Using replace would have been limited to fixing one date at a time to avoid removing zeros you wanted to keep.

[DanPatterson_Retired]

>>> a = "011/12/2014"
>>> a
'011/12/2014'
>>> b = a.replace("011/12/2014","11/12/2014")
>>> b
'11/12/2014'
>>> a.replace("011/12/2014","11/12/2014")
'11/12/2014'
>>> a
'011/12/2014'
>>> # hmmmm thought I changed me????
a = a[1:]
>>> a
'11/12/2014'

Have you tried to add a new field and send the calculation there, since overwriting itself doesn't appear to be the way to go. EDIT slice instead

[DarrenWiens2]

Hmmm I think you can replace in place, and similar in the field calculator...

>>> a = "011/12/2014"
>>> a
'011/12/2014'
>>> a = a.replace("011/12/2014","11/12/2014")
>>> a
'11/12/2014'