topic Re: Using the SQL [charlist] wildcard in Data Management Questions

Using the SQL [charlist] wildcard

TychoGranville — Tue, 09 Jan 2018 18:06:41 GMT

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

Re: Using the SQL [charlist] wildcard

JimCousins — Tue, 09 Jan 2018 19:01:25 GMT

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

Re: Using the SQL [charlist] wildcard

TychoGranville — Tue, 09 Jan 2018 20:22:02 GMT

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

Re: Using the SQL [charlist] wildcard

JoeBorgione — Tue, 09 Jan 2018 20:55:27 GMT

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

Re: Using the SQL [charlist] wildcard

DanPatterson_Retired — Tue, 09 Jan 2018 21:11:58 GMT

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

Re: Using the SQL [charlist] wildcard

JoeBorgione — Wed, 10 Jan 2018 00:05:14 GMT

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.

Re: Using the SQL [charlist] wildcard

DanPatterson_Retired — Wed, 10 Jan 2018 00:28:24 GMT

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

Re: Using the SQL [charlist] wildcard

JoshuaBixby — Tue, 22 Jun 2021 08:42:20 GMT

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

Re: Using the SQL [charlist] wildcard

TychoGranville — Wed, 10 Jan 2018 18:02:29 GMT

Thanks, all.

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