nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

bixb0012

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

All comments

TychoGranville

Thanks, all.

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

bixb0012

I recently posted an idea requesting additional support for pattern matching with file geodatabases: https://community.esri.com/ideas/14378

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

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

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

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

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.