SQL queries not working in Arcpy but working in Select by Attributes window

Question

I have run into this odd issue where my SQL query is accepted and run correctly through the Select by Attributes window but produce an empty output when used in the python script I'm writing. What makes this weirder is that similar queries that use '=' instead of 'LIKE' work perfectly fine in the script. I am using ArcGIS Desktop 10.8.1.

arcpy.SelectLayerByAttribute_management("T_facility", "NEW_SELECTION", "FACILITYID LIKE '%V%' OR FACILITYID LIKE '%B%'")

Even when I separately use the two qualifiers of 'V' and 'B', only 'B' produces an actual selection in the script. I should also mention that most, if not all, of the rows that I'm selecting with the 'V' qualifier have 'V' as the first character.

I had previously been using Table to Table but when that started creating empty outputs, I broke it into parts with Select by Attributes + Copy Rows. Here is an example of what previously worked (filepaths not accurate to actual script):

arcpy.TableToTable_conversion("//Documents/ArcGIS/Default.gdb/Connection", "C:\my_name\F_ID.dbf", "MidSS", "SUBTYPECODE = 4")

Does anyone have an explanation as to why these selections suddenly produce nothing through python while working manually in Select by Attributes?

rzufelt · Answer

Not sure what's going on then.  Thought in the past I had issues using '%V%' if there were no other characters before the 'V', but can't reproduce it.  Your query (modified slightly with my data) seems to be working as expected.  If LIKE won't select any of the  V's, maybe there is a character issue or something.

As a test, you might try to edit a few of them manually and replace a few of the V's by typing over them and see if they get selected in case there is something weird with the characters.

R_

I'm still on 10.6.1.

RhettG · Answer

Yes, I am running it inside ArcMap 10.8.1. My process has been Statistics_analysis -> MakeTableView -> TableToTable with my SQL statements used in the TableToTable step. This process was working perfectly until I started using the "LIKE '%V%'" in my SQL statement. It started producing empty outputs so I tried doing SelectLayerByAttribute -> CopyRows as an alternative but it still isn't working. So I already made it a Table View before trying to apply the SQL statement.

rzufelt · Answer

Are you running inside of Pro/Map?  When in ArcMap or Pro, picking data listed in the TOC is already a feature layer/view. (converted in the background when loaded into the Map)

Select Layer by Attributes takes a layer or table view, not a feature class or table directly:

* The input must be a feature layer or a table view. The input cannot be a feature class or table

If you are trying to run it standalone, you will need to the Make Feature Layer or Make Table View first (using full path to the data), then pass that "layer" to the Select by Attributes.

R_