0

This is part of a much larger project in modelbuilder. I want to use the make feature layer tool to query a field that has multiple values in each record. For example, the field name is "Animal" and the value would be "zebra, cat, horse, dog" (without quotes) for each record. How can I setup the query to search for all the animals? There are some records that only have one value in which case I can just set the query to = the field but when the field has multiple entries for each records, I'm not sure how to setup the query. I need something like "Animal" IN ('zebra', 'cat', 'horse', 'dog') But how would I add the single quotes and the parenthesis to the field value(s)?

I'm using ArcGIS 10.2.2 and all values in the field are separated by a comma and a space.

Edit: I should have been a little more clear in my original post. This query is going thru an iterator and each time the query will be different (there are hundreds of animals). The field itself has to pass thru the iterator for the query so I'm wondering how I can accomplish this. I've included a screenshot of the current model. Circled in red is where the field passes thru the "Get Field Value" tool and I then need to use that value to query a separate layer of "animals" and select all the animals in the "animal" layer there are in the "animal" field from the first layer. In essence I need to figure out how the field can be modified to feed directly into the query.

enter image description here

  • 2
    If you are searching for any combination of animals use OR and LIKE with the appropriate wildcard for your database. For a File Geodatabase the query would be: Animal LIKE '%zebra%' OR Animal LIKE '%cat%' OR Animal LIKE '%horse%' OR Animal LIKE '%dog%' – Richard Fairhurst Apr 17 '18 at 21:31
  • 1
    My comment would require each animal name to not be part of another animal name. For example, if some fields had cat and others had catfish and you only want cat you would have to put my previous query in parentheses and add in front of the other query NOT Animal LIKE '%catfish%' AND ... But if the field had both cat and catfish in it, this clause would exclude that record. It is still possible to handle that situation with more clauses, but it can get complicated and may be better to approach with Select from Current Records or Remove from Current Records queries. – Richard Fairhurst Apr 17 '18 at 21:45
  • 1
    @RichardFairhurst those two comments constitute a good and complete answer, can you answer this post please. One thing extra I could add is that file geodatabase SQL is case sensitive (not personal geodatabase) so it would be best to stick to one case or the other: lower(Animal) LIKE '%zebra%' OR lower(Animal) LIKE '%cat%' just in case the field contains 'Zebra' or 'ZEBRA', this will slow down the query a bit; another viable alternative is to calc Animal = !Animal!.lower() (python parser) before querying to ensure that the case of all the animals is lower case. – Michael Stimson Apr 17 '18 at 23:05
  • @MichaelStimson Thanks for the comment. I have posted my comments as an answer and included your suggestion about standardizing the case of the field. I also corrected a few of my statements, since in reality the exclusion clause I added would exclude any of the animals names listed if catfish was also included in the record. – Richard Fairhurst Apr 18 '18 at 0:36
  • I resolved this by creating a new animal field and calculating it from the original animal field in two queries, one in VBA and the other in Python. <br/> "('" & [AnimalField] & "')"<br/> and <br/> !AnimalField!.replace(", ", "', '") – Steve Apr 19 '18 at 17:57
2

If you are searching for any combination of animals use OR and LIKE with the appropriate wildcard for your database. Also, as Michael Stimson pointed out, most (but not all) databases are case sensitive, so it may be a good idea to include an SQL method for standardizing the case of the field. For a File Geodatabase the query to do what you want would be:

lower(Animal) LIKE '%zebra%' OR lower(Animal) LIKE '%cat%' OR lower(Animal) LIKE '%horse%' OR lower(Animal) LIKE '%dog%'

The SQL above would require each animal name to not be part of another animal name. For example, if some fields had cat and others had catfish or bobcat and you only want cat you would have to handle the search for cat as an exact match as well as the cases where other animals are listed and cat is the first, middle and last word in the field, i.e.:

lower(Animal) LIKE '%zebra%' OR lower(Animal) = 'cat' OR lower(Animal) LIKE 'cat,%' OR lower(Animal) LIKE '% cat,%' OR lower(Animal) LIKE '% cat' OR lower(Animal) LIKE '%horse%' OR lower(Animal) LIKE '%dog%'

Depending on the values you are dealing with, creating an SQL statement that does exactly what you want can get long and complicated and it is easy to get too many or too few records when there are a lot values that are parts of other words. If that occurs a lot, a better approach may be to use additional separate Select Layer from Attribute queries using the Select from Current Records, Add to Current Records or Remove from Current Records options.

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.