2

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?

  • If you know the integers, you can simply use "INT Field" NOT IN (18, 181, 18111) – enolan Aug 22 '18 at 14:59
  • 1
    Please remember to specify the data source in your question, since the syntax is different by source. – Vince Aug 22 '18 at 20:05
5

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

1

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'
  • Definition queries are part of the properties of a layer in an mxd. – enolan Aug 22 '18 at 15:43
1

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.

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.