I recently ran into an issue that caused me to review the SQL reference for query expressions used in ArcGIS—Help | ArcGIS Desktop. In the Strings section of the SQL reference I came across the following line:
Refer to the documentation of your DBMS for a list of supported functions.
I decided to take that advice and look up supported string functions with various DBMSs, particularly pattern matching with the SQL WHERE clause:
Looking at the list, one might ask, "where is the link for file geodatabases?" My response, "exactly, where is that documentation?" There are some pages that provide examples and speak around the issue, e.g., Building a query expression and SQL reference for query expressions used in ArcGIS, but I haven't been able to find a page that definitively and succinctly states the support like all of the other pages referenced above.
If one were to find the documentation (by the way, please share the link if you do), what would become clear is that file geodatabases have rudimentary support for pattern matching in strings. The only other DBMS format, if we can call it a DBMS, with such limited support is the shape file. Given when shape files were defined/introduced, I am willing to give a pass for them, but I can't say the same for file geodatabases.
The file geodatabase introduced some big improvements over the personal geodatabase when working with spatial data, but it wasn't and still isn't summarily better because it had to make some trade offs. For all the promise that came with the original release of the file geodatabase, there has been an equal amount of disappointment over the lack of effort to continuing improving the format.
It is time for file geodatabases to enter the 21st Century and support regular-expression pattern matching with strings, or at least regular-expression-like functionality.
