ArcGIS Desktop 10.3 - definition query to exclude integers starting with certain values

Question

I have an integer field in a polygon shapefile that I want to definition query out everything number that beings with 18. These numbers aren't sequential and are of any length, ie I want to exclude 18, 181, 18111, etc but not the numbers in between that don't start with 18.

After some research and going over this SE thread I'm not sure this is possible. Any ideas?

Answer 1

You can use cast and like:

CAST("Somefield" AS CHARACTER(3)) NOT LIKE '18%'

See Using SQL CAST functionality in against a File Geodatabase

You might need to change the double quotation marks to single or remove them completely around your fieldname depending on your input (shapefile, feature class in fgdb, etc).

Answer 2

For PostgreSQL you could try this:

SUBSTRING(CAST(myfield AS VARCHAR), 1, 2) = '18'

I haven't tried Oracle or SQL Server, but I'm sure there exists a similar approach, if this doesn't work.

With a file Geodatabase, you must specify a length when casting, for an enterprise geodatabase the length is optional:

SUBSTRING(CAST(myfield AS VARCHAR(12)), 1, 2) = '18'

Answer 3

You can create a new text field and calculate all the integer field records in it, then you can use NOT LIKE statement with a "%" to query your record. Like:

"YourStringFieldName" NOT LIKE '18%'

In "LIKE" statements, "%" is used to match any character or number of characters, where as "_" is used to match a single character at a specific placement.