SQL definition query list

Posted by in ArcMap, GIS

Here’s a quick one, I see a lot of sql expressions written like this:

Name = “Roosevelt” OR Name = “Jackson” OR Name = “Jefferson” OR Name = “Lincoln” OR Name = “Franklin” OR Name = “Washington”

This can be written using the IN function. This is a little more legible and easier to compose – same expression as above, written differently:

Name IN (“Roosevelt”,“Jackson”,“Jefferson”,“Lincoln”,”Franklin”,“Washington”)

It’s a bit easier to edit a long list of choices this way.

Also, you can write a list of specific features to exclude, the following will exclude the features within the list:

NOT Name IN (“Roosevelt”,“Jackson”,“Jefferson”,“Lincoln”,”Franklin”,“Washington”)

You cannot use a wildcard in this list.. If you want to introduce a wild card (%) in a sql expression you must use LIKE and keep it separate from the list.

(Name LIKE “%enn%”) OR (Name IN “Roosevelt”,“Jackson”,“Jefferson”,“Lincoln”,”Franklin”,“Washington”))

The expression above will return every feature with ‘enn’ anywhere in the name plus the names in the list.

1 Comment

  1. Langdon
    May 15, 2014

    Good tip! I use this regularly. Another note, if you receive a SQL expression error, it is likely you forgot a quotation mark or comma somewhere in the list.

    Reply

Leave a Reply