I need to isolate those records containing numbers from a field with character string (it's a descriptive field). For example the string might be as follows:

"A residential development of 12 no. two storey dwellings comprising 10 no. 4 bedroom semi-detached houses and 2 no. detached 3 bedroom houses all with associated on-curtilage parking."

Table name: PLANAPP_iPlan_Merged

Field Name: Description

The numbers can occur anywhere in the string so I can't set Left%/ Right$ parameters. Anyone any ideas?


This answer is for MapInfo Professional but there should be a similar method possible in ArcGIS.

You can do this using the following SQL query. It checks whether each numeric digit exists in the string and returns its position if it does and 0 if it is not found. If any exist then the result of the addition should be greater than 0.

FROM PLANAPP_iPlan_Merged 
WHERE (InStr(1,Description,"0") + InStr(1,Description,"1") + 
       InStr(1,Description,"2") + InStr(1,Description,"3") + 
       InStr(1,Description,"4") + InStr(1,Description,"5") + 
       InStr(1,Description,"6") + InStr(1,Description,"7") + 
       InStr(1,Description,"8") + InStr(1,Description,"9")) > 0
  • 1
    That is amazing!! You're a pure legend - thank you - worked like a dream :) – Irene D'Arcy Mar 28 '17 at 10:49
  • @IreneD'Arcy Great, glad to help! Can you be a legend yourself and mark it as the accepted answer please? :-) – T_Bacon Mar 28 '17 at 11:14
  • Neat solution, @T_Bacon! – Peter Horsbøll Møller Mar 28 '17 at 13:48

In ArcGIS you can use the Field Calculator. Add a new field with data type Short and calculate. You can then use this field to select all rows with numbers:

enter image description here

Pre-Logic Script Code:

def hasNumbers(inputString):
    return any(char.isdigit() for char in inputString)

And below (replace String with the name of your field):

  • Thanks so much for your help - we're hoping to move to Arc later this year so this will be great to have! – Irene D'Arcy Mar 28 '17 at 10:49

If you need a list of all the numbers as they are in the description field. add a new field of type Text, and in the Field calculator do the following after selecting Python parser:

Pre-Logic Script Code:

def get_num(field):
    number = [str(s) for s in field.split() if s.isdigit()]
    return str(number) 

Write under the New_Field =

get_num(!Decr!)  #Change !Decr! with !Description! in your case

The result will be as follows:

['12', '10', '4', '2', '3']

enter image description here


With Python, you can use a regular expression and a Search Cursor to extract integers from a string:

import arcpy, re

fc = r'C:\path\to\your\fc'

with arcpy.da.SearchCursor(fc, "some_field") as cursor:
    for row in cursor:
        print row[0]
        print re.findall('\d+', row[0])

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.