Finding only records with numbers in a character string field?

Question

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?

Answer 1

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.

SELECT * 
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

• Irene D'Arcy: That is amazing!! You're a pure legend - thank you - worked like a dream :)
• T_Bacon: @IreneD'Arcy Great, glad to help! Can you be a legend yourself and mark it as the accepted answer please? :-)
• Peter Horsbøll Møller: Neat solution, @T_Bacon!

Answer 2

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

Answer 3

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])