Using select by attributes in ArcGIS Pro ModelBuilder

Question

Summary of my problem:

When I use the select by attributes option directly from the attribute table, my query works perfectly, however, if I run the exact same query from the ModelBuilder. It doesn't select any of the features I want.

Am I doing something wrong, or is this a bug within ArcGIS Pro 2.9.5?

This even happens when I save my query and open it from the ModelBuilder.

Some more background:

• I try to do a query based on a column that contains the building name
• If the building name of the attribute contains the word "MRT", the feature should be selected. I use the "contains the text" MRT query.
• I am selecting about 200 out of 60000 features when I use the select by attributes tool within the attribute table. I select 0 features when I do the same within the ModelBuilder.

it works when running the query from the attribute table

I use the exact same query within the model builder

However... 0 selections when using the model builder

• Lau: My own guess is that this is a bug, but I am not sure. It seems like Model Builder is not capable of doing queries with upper case letters. It keeps changing MRT back to mrt. I guess it doesn't find any matches because the text is all upper case in the attributes.
• Hornbydd: What's the source of your data, is it some SDE layer? This is a basic query and I certainly never observed such behaviour whilst I was using 2.9
• Lau: @Hornbydd, thanks for taking the time to answer! It is a feature class stored in a GDB so I don't think it is an SDE? (I am not familiar with SDE's).
• Hornbydd: Try simplifying the group layer and layer name, don't have spaces, colon characters or any other non alphabet characters?
• Lau: @Hornbydd, thanks. I tried this, but it is also not working. Also changing the text to lower case and running the SQL query again doesn't work.

Answer 1

SQL queries run on string (text) field in ArcGIS Pro are case sensitive. Have a look at the documentation here.

In your first screenshot of the query,"MRT" is in all caps. Your query is equivalent to NAME LIKE '%MRT%'.

In your second query you're running in ModelBuilder, "mrt" is in lowercase. Your query is equivalent to NAME LIKE '%mrt%'.

The most likely explanation is that your BUILDING_N field contains the string "MRT" in all caps, but not in lowercase. So in the first query you get the expected matches but in the second you return an empty found set because "mrt" doesn't exist in any of the entries.

For example, here is a similar query run on a shapefile containing state outlines. Searching for "Virg" in the NAME field returns 3 results -- West Virginia, Virginia, and the US Virgin Islands. Searching for "VIRG" returns 0 results, as that string field contains mixed case text.

enter image description here

enter image description here