topic Re: Why does FIELDNAME LIKE '% %' not work in 10.3.1 file geodatabase table? in Data Management Questions

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

RichardFairhurst — Mon, 06 Mar 2017 20:39:46 GMT

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?

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

JoshuaBixby — Mon, 06 Mar 2017 20:47:06 GMT

I just tested Make Feature Layer using ArcGIS 10.5 using "LIKE '% %'" in the where_clause and it worked correctly. I have access to 10.3.1 that I can test later. What tools are you using to query the file geodatabase?

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

DanPatterson_Retired — Mon, 06 Mar 2017 20:49:20 GMT

don't have that version, but emulate to ensure that there is possibilities and you are typing as little as possible. In my example, the only thing I typed were the single quotes and the space.

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

RichardFairhurst — Mon, 06 Mar 2017 20:51:59 GMT

I am just using the Search by Attributes dialog within ArcMap Desktop (from the table view menu). The queries are all being done interactively during a QC process. I pretty much always use the field list to build my query expression and even click the LIKE button. I only type '% %'. I will try using the % button. I have not tried to script this type of query.

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

JoshuaBixby — Mon, 06 Mar 2017 20:56:40 GMT

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.

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

DanPatterson_Retired — Mon, 06 Mar 2017 20:58:14 GMT

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

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

RichardFairhurst — Mon, 06 Mar 2017 21:08:51 GMT

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.