topic Re: Query a string field for numbers in Data Management Questions

Query a string field for numbers

GlennKammerer — Thu, 12 Jan 2012 16:30:36 GMT

We are encountering a geocoding issue with a client's road centerline data. They are using NENA standard road name fields, but some of the value in the STREET_NAME field are "2095", "1875", "1560", etc. The problem is 1) while the fields are NENA standard these values are not, and 2) the geocoder is interpreting these values as numbers and thus barfing all over itself.

So...and I'm not holding my breath...is there a way to query the STREET_NAME field, which is defined as text, for "numeric" values, or numbers stored as text, WITHOUT writing a script?

Any help would be greatly appreciated.

Using ArcEditor 10.0 SP 2, Windows XP Pro SP3.

Re: Query a string field for numbers

RichardFairhurst — Thu, 12 Jan 2012 19:14:35 GMT

We are encountering a geocoding issue with a client's road centerline data. They are using NENA standard road name fields, but some of the value in the STREET_NAME field are "2095", "1875", "1560", etc. The problem is 1) while the fields are NENA standard these values are not, and 2) the geocoder is interpreting these values as numbers and thus barfing all over itself.

So...and I'm not holding my breath...is there a way to query the STREET_NAME field, which is defined as text, for "numeric" values, or numbers stored as text, WITHOUT writing a script?

Any help would be greatly appreciated.

Using ArcEditor 10.0 SP 2, Windows XP Pro SP3.

You did not say what kind of database you are using, and solving your problem may be SQL specific. In a file geodatabase I tried this expression on a string field and it seemed to work properly. See if something like this works for you (perhaps export to a fgdb if your native source does not support the cast operation).

CAST("STREET_NAME" AS FLOAT) < 9999

Re: Query a string field for numbers

JoeBorgione — Thu, 12 Jan 2012 20:37:34 GMT

I've found that FGDBs don't like the 'LIKE' statement; In a pgdb you can use Name like '[0-9]*' to find those that start with a number. I don't know if that is a VB only type of query or not, so your mileage may vary in version 10. If that works for you, so will Name like '[A-Z]*' . For a SDE GDB with a SQL Server back end you'll need to use % instead of *.

Hope this helps.

Re: Query a string field for numbers

GlennKammerer — Fri, 27 Jan 2012 17:48:08 GMT

Sorry for the much delayed follow up.

I ended up teaching myself how to do this in Python and used the IsDigit function in Python. Worked like a charm!

Thank you jborgion and rfairhur24 for your replies.