I'm (attempting to) use the select by attributes function in a 10.5.1 MXD. I tried both a file GeoDatabase and our Enterprise database The select by list statement does not seem to work. When I put in this –
Taxlot.MapTaxlot LIKE '%[RWT]%'
it does not return any records. I pulled the % off the end as some of the strings end with those letters, and tried using parentheses instead of square brackets. I also tried
Taxlot.MapTaxlot IN ('%R%', '%W%', '%T%') - which selects nothing and
Taxlot.MapTaxlot NOT IN ('%R%', '%W%', '%T%') - which selects everything
The list wildcard seems to be pretty standard in SQL, but I didn't see anything about it in the ESRI documentation (not that that would be the first time I missed something).
Thanks,
Tycho
Solved! Go to Solution.
As Joe Borgione already mentioned, the file geodatabase is a fairly limited/weak DBMS and it lacks quite a bit of SQL support common in nearly all other "modern" DBMSs, even file-based ones. For your specific situation, the file geodatabase does not support pattern matching, i.e., regular expression-like matching, with the LIKE clause, but of course SQL Server does. In light of that, you will have to go with the expanded OR expressions.
I recently posted an idea requesting additional support for pattern matching with file geodatabases: https://community.esri.com/ideas/14378
Some information for you:
Building a query expression—Help | ArcGIS for Desktop
Geodatabase uses a * as the wildcard. Also, when searching a string, you need to be careful that your wildcard does not get read as actually part of the string.
Best Regards,
Jim
I probably didn't compose my original post very well. It is a FileGeoDB so:
SELECT * FROM Taxlot_Taxpayers WHERE
Taxlot.MapTaxlot LIKE '%R%'
Does return all records in that field that contain an R.
What I have now (which works) is:
SELECT * FROM Taxlot_Taxpayers WHERE
Taxlot.MapTaxlot LIKE '%R%' OR Taxlot.MapTaxlot LIKE '%W%' OR Taxlot.MapTaxlot LIKE '%T%'
What I want is a shortend version like this ( Which should work with SQL):
Taxlot.MapTaxlot LIKE '%[RWT]%'
It doesn't matter that much with this example, but if I end up with a lot of values it becomes a pain (and is harder to read as well).
Like '%R%W%T%' doesn't work in your filegeodatabase? your inclusion of the square braces certainly doesn't
Wouldn't that just find a record where the field started with 'something', and an R in it, that was then followed by something else, and has W in it and is followed by something else and then has a T, and is followed by something else.
"ToughRugbyWithTattoos"
My money is on the multiple or statements mentioned earlier: they are a pain, but I think they will work.
hmmm at least in ArcMap the position of the % can mean nothing as in something or nothing
As Joe Borgione already mentioned, the file geodatabase is a fairly limited/weak DBMS and it lacks quite a bit of SQL support common in nearly all other "modern" DBMSs, even file-based ones. For your specific situation, the file geodatabase does not support pattern matching, i.e., regular expression-like matching, with the LIKE clause, but of course SQL Server does. In light of that, you will have to go with the expanded OR expressions.
I recently posted an idea requesting additional support for pattern matching with file geodatabases: https://community.esri.com/ideas/14378
In a personal geoddatabase (aka access mdb) the asterisk is the universal wild card. However in enterpise and file geodatabases, the percent sign is your guy. However, that said, file geodatabases are not truly SQL compliant, and the your [ ] trick that may work in a SQL db will not work in a file geodatabse. For example, try Like '[0-9]%' (those records that start with a numeric character) in a file gdb and you won't get anything...
Thanks, all.
As I learn more SQL it just gets me into trouble. I will continue on with my clunky coding