Using the SQL [charlist] wildcard

[TychoGranville]

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

[JimCousins]

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

[TychoGranville]

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).

[DanPatterson_Retired]

Like '%R%W%T%' doesn't work in your filegeodatabase? your inclusion of the square braces certainly doesn't

[JoeBorgione]

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.

[DanPatterson_Retired]

hmmm at least in ArcMap the position of the % can mean nothing as in something or nothing

[JoshuaBixby · Accepted 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 

[JoeBorgione]

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...

[TychoGranville]

Thanks, all.

As I learn more SQL it just gets me into trouble. I will continue on with my clunky coding