Why does FIELDNAME LIKE '% %' not work in 10.3.1 file geodatabase table?

Question

I am very frustrated that whenever I try to query for a space character using a LIKE query that I never get any results from a file geodatabase table.  If I query FIELDNAME LIKE '% %' nothing is returned, even though I know there are values that match that criteria (most of them do).  I do not recall having this problem in 10.3 or 10.2, although it has been a while since I have had those versions.

Does this type of query work in ArcMap 10.3.1?  If others do not have this problem then perhaps the database needs repair, but I want to know that it works for someone before trying that route since the tables are large.

If it doesn't work how do I find records with two or more words separated by spaces in a fields using a query on a 10.3.1. file geodatabase table?

RichardFairhurst · Answer

When I try FIELDNAME IN (' ') all I get are fields that have no characters (but not Null) or that have any number of space characters, but no words.

I think the table may be corrupt.  It was converted from another database using a CSV file that included several special characters.  While I manually removed those I could find that popped up when I converted back to a CSV file using Python (which didn't like them), I did not try to clean up the original CSV file and start the conversion fresh.  There may still be some hidden characters that have damaged the way SQL reads the table with this particular query.

At this point I have done too much editing to start fresh from the original file, but I think exporting the data will work or else converting to the CVS file with the Python script and then using that file to recreate the table.

DanPatterson_Retired · Answer

try ... Sector IN (' ') ... where sector is the fieldname from the previous example and a space is between the quotes.

JoshuaBixby · Answer

I just used the Select By Attributes dialog box on the same feature class using the same SQL, and it returned the same result, i.e., it worked correctly.  I wonder if there is a bug with 10.3.1.  I will have to check it out and post back my results.