2

I'm using ArcMap 10.2 and I'd like to write a query to select features where Field A does not include text found in Field B.

Specifically, Field A contains street numbers and street names. Field B contains only street names. I'd like to select features where Field A does not include the street names in Field B.

So far I've tried -

"Field_A" NOT LIKE'% "Field_B"%'
  • For this question, you will likely get a more useful answer if you specify your data source, are you using shapefile, personal GDB, File GDB, or some enterprise sde gdb (if so, which database system SqlServer, Oracle, etc...). Data source can be important for sql syntax. – John Mar 16 '16 at 21:25
3

It looks like the syntax you are using above is for a File GDB. If this references a feature class in SQL Server, I believe the syntax would be something like this:

Field_A NOT LIKE '%' + Field_B + '%'

This should work in the select by attribute tool or you could also set this as a definition query on the layer and work with the results that way as well.

I believe string concatenation is handled differently in Oracle so I'm not sure about that syntax.

  • Thank you so much for the suggestion - I'll give it a try and let you know how it goes! – Bryn Murphy Mar 16 '16 at 22:36
  • is there anyway to do the above querry using the IN operator? – ziggy Mar 17 '16 at 1:45
  • @ziggy There may be a way to do it with the IN operator but it would likely still require the use of some sort of string search function. Someone more clever with SQL than I would have to chime in. If you don't want to use LIKE, you could look into the CHARINDEX or CONTAINS functions. You may also want to try it as a query layer rather than select by attribute since that gives you more flexibility with the SQL you can use. – Brenner256 Mar 17 '16 at 11:27
  • 1
    @ziggy There's not really good ways to use "Like" and "In" in the same simple query, you have to get into sub-queries to get that effect. Here is some rough code you could play with that does I think what you're wanting aproximately, but it won't run well in select statements, you'd be better making a query layer for more complex SQL like this. SELECT A.* FROM TABLE_1 AS A WHERE (Exists (SELECT 1 FROM TABLE_1 AS B WHERE A.FIELD3 LIKE '*' + B.FIELD4 + '*')) (This is only sample code though, you'll have to adjust to meet your needs). – John Mar 17 '16 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.