Code Help

08-21-2015 02:32 PM
New Contributor II

I'm in need of some help.  I have a file with a attribute field named SPCOMP and I need to separate it out.  The SPCOMP field is populated with a string such as "Sb  90Bf  10" or Pj  80Sb  20" or Pj  40Bw  40Sb  20"  etc.  I'm trying to write a code using the field calculator in VB

script that will search through the SPCOMP field for "Sb  90" and populate another field SB with 90 or search for "Sb  60" and put 60

in the SB field.  My code is:

Dim SB

if [SPCOMP] = "%Sb  90%" Then

SB = 90

elseif [SPCOMP] = "%Sb  60%" Then

SB = 60


SB = 1

end if

When I run this it will populate the SB field with 1 so I think my mistake is with the wildcard search?  Can anyone offer suggestions on this or where I could find answers?  I'm using ArcGIS advanced 10.3.


Tags (1)
0 Kudos
9 Replies
MVP Legendary Contributor

Like operator perhaps?  from the 10.3 help files...but nothing has changed you can try "building a query expression" or "wildcard" as search terms in your help

Building a query expression—Help | ArcGIS for Desktop

Use the LIKE operator (instead of the = operator) to build a partial string search. For example, this expression would select Mississippi and Missouri among U.S. state names:


% means that anything is acceptable in its place: one character, a hundred characters, or no character. Alternatively, if you want to search with a wildcard that represents one character, use _.

For example, this expression would find Catherine Smith and Katherine Smith:

OWNER_NAME LIKE '_atherine smith'

The wildcards above work for any file-based data or ArcSDE geodatabase. The wildcards you use to query personal geodatabases are * for any number of characters and ? for one character.

0 Kudos
Esri Esteemed Contributor

If you are interested in using the Python syntax you could use this:

def getSB(spcomb):
    if 'Sb  ' in spcomb:
        n = spcomb.find('Sb  ')
        return spcomb[n+4:n+6]
        return 0

getSB( !SPCOMP! )

and the result:

Before you try this, you ask this question: Is it always a number between 10 and 99 (two digits)?

... it will fail if:

  • the number is <10 and > 99
  • if it isn't a number (should check to see if it is numeric)
  • Code wil not find 'SB' or 'sB' or 'sb', it is case sensitive
  • etc...
New Contributor II

I have tried the LIKE operator in VB but the error message is always 99999 (general error) I tried may different ways but the same result.  If I type the SPCOMP code in exactly  then it works.

(ie: if [SPCOMP] = 'Sb  90Bf  10')

I tried the python but it does not work either.  I never used python before and don't understand it either.  Maybe I should though.

And yes the string is exact (it's either SB or Sb but never both in the same file.)

in the VB calculator, this is my code

Dim SB

if [SPCOMP] LIKE '%Sb 100%' Then
SB = 100

elseif [SPCOMP] LIKE '%Sb  90%' Then
SB = 90

elseif [SPCOMP] LIKE '%Sb  80%' Then
SB = 80

elseif [SPCOMP] LIKE '%Sb  70%' Then
SB = 70

elseif [SPCOMP] LIKE '%Sb  60%' Then
SB = 60

elseif [SPCOMP] LIKE '%Sb  50%' Then
SB = 50

elseif [SPCOMP] LIKE '%Sb  40%' Then
SB = 40

elseif [SPCOMP] LIKE '%Sb  30%' Then
SB = 30

elseif [SPCOMP] LIKE '%Sb  20%' Then
SB = 20

elseif [SPCOMP] LIKE '%Sb  10%' Then
SB = 10

SB = 0
end if

in the lower box (SB=)


I posted my .gdb here as a zip file.

0 Kudos
Regular Contributor III

Use select by attributes to get a selection then use field calculator to populate the intended field

fieldname  LIKE '%90%'

Then field calculate the selection set to 90

repeat for 60

0 Kudos
Esri Esteemed Contributor

Could you attach the ZIP to the thread (click on advanced editor upper right corner and then on Attach in lower right corner), since I wasn't able to download your file:

0 Kudos
New Contributor II

Thanks I didn't know how to attach a file.

Wes suggested select by attributes then using the calculator and I know that works but there are 14 species and 10 percentages each so that tends to become tedious after a while.  The combination I'm looking for is Sb  90 or SB  80 or PO  20 or LA  70  etc   Hopefully, the attached file will help.  I can do this in MS excel but I would like to do it in ARCmap

0 Kudos
Esri Esteemed Contributor

The code I posted earlier seems to work on your data:

... however, looking at your data and the fact that you want to apply this to multiple fields, I think using Python code and an update cursor would be easier...

Edit: I see that the result is wrong for "Sb 100"

0 Kudos
New Contributor II

Yes, this does work, Thanks.  I even modified it a bit for other species and it works.

def getSB(spcomb): 

    if 'Sb' in spcomb: 

        n = spcomb.find('Sb') 

        return spcomb[n+3:n+6] 


        return 0

I don't understand what "return spcomb[n+3:n+6]"  is but I changed the n+4 to n+3 and the 'Sb  ' to 'Sb' and it worked for the Sb 100 so I'm assuming it relates to 6 spaces to the right of Sb and 3 back from that.

Thanks to everyone for helping me with this.

0 Kudos
Esri Esteemed Contributor

As mentioned before, I would probably use some Python code to do the job:

This table was updated using this code:

def main():
    import arcpy
    fc = r"C:\GeoNet\SPCOMP\ARC.gdb\SPCOMP"
    fld_in = "SPCOMP"

    # create list of fields with length 2
    flds = [ for fld in arcpy.ListFields(fc) if len( == 2]
    flds.insert(0, fld_in)

    # start update cursor
    with arcpy.da.UpdateCursor(fc, flds) as curs:
        for row in curs:
            spcomp = row[0].upper()
            for fldname in flds[1:]:
                if fldname.upper() in spcomp:
                    perc = getPercentage(fldname.upper(), spcomp)
                    row[flds.index(fldname)] = perc

def getPercentage(fldname, spcomp):
        n = spcomp.find(fldname)
        part = spcomp[n+2:n+6]
        return int(part)
        return -1

if __name__ == '__main__':

Some assumptions that seem to be true for this table:

  • field names that need to be updated are two characters long and correspond to the specie
  • a species inside the field SPCOMP always occupies 5 characters.