Select to view content in your preferred language

ArcGIS Select Query help

1466
6
Jump to solution
05-30-2018 08:28 PM
AnuSawant
Frequent Contributor

Hi there,

In one of my field I have got records showing attribute 

1) A1,F4,A10

2) A1,A17

3) F1,G6, A1

4) A10, A1

6) D1, A17

7) D2, D4

and so on I am trying to select records showing A1 valueI have tried using query  Field1 LIKE '%A1%' it is also selecting A10. Is there any way I can select only value A1?

Thanks,

Anu

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

As Vince already pointed out, your data design doesn't fit well within the relational model, which makes storing and querying it in a relational data store kludgy.  That said, it is not uncommon to see this kind of data design, and most relational data stores and the SQL standard itself have made some accommodations.

Trying to use SQL LIKE to accomplish your matching will result in a long list of LIKE and OR statements.  Depending on your backend data store, you can use expanded pattern matching through LIKE or SIMILAR.  The not-so-good news is that expanded pattern matching is very implementation/product specific, so portability suffers.

If you are working with file geodatabases, see if the following works:

-- SIMILAR TO syntax here only works correctly in 
-- ArcGIS Desktop 10.6 or greater and ArcGIS Pro 2.0 or greater
field_name SIMILAR TO '(A1[ ,]%|%[ ,]A1[ ,]%|%[ ,]A1)'‍‍‍‍  

Looking closely at your data, the matching is made more difficult by inconsistent use of spaces.  For some records, there is a space after a comma but not all the time.  My SQL above assumes there may or may not be a single space or comma before or after the value you are searching.

UPDATE:

The comparable SQL to my above using basic pattern matching with LIKE is:

field_name LIKE 'A1,%' OR
field_name LIKE 'A1 %' OR
field_name LIKE '% A1 %' OR
field_name LIKE '% A1,%' OR
field_name LIKE '%,A1 %' OR
field_name LIKE '%,A1,%' OR
field_name LIKE '% A1' OR
field_name LIKE '%,A1'‍‍‍‍‍‍‍‍

The SQL code using LIKE is very portable, but as you can see, you shouldn't expect much for performance when you chaining together so many condition checks using OR.

View solution in original post

6 Replies
DanPatterson_Retired
MVP Emeritus

drop the 2nd %

Field1 LIKE '%A1'

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Unfortunately, dropping the second wildcard will end up missing cases where A1 is not at the end of the string.

0 Kudos
DanPatterson_Retired
MVP Emeritus

Ahhhh he didn't indicate that possibility

Just going with the KISS principle first

0 Kudos
VinceAngelo
Esri Esteemed Contributor

This is why lists are not considered best-practice in database design. 

If you had a second table organized:

keycolattrval
1A1
1F4
1A10
2A1
2A17
3F1
3G6
3A1
4A10
4A1

then you could fashion a query:

SELECT *

FROM mytable

WHERE keycol in (

   SELECT keycol

   FROM secondtab

   WHERE attrval = 'A1')

- V

AnuSawant
Frequent Contributor

Hi Vince,

Unfortunately this is third party data and I have to process it without doing any changes. 

Thanks ,

Anu

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

As Vince already pointed out, your data design doesn't fit well within the relational model, which makes storing and querying it in a relational data store kludgy.  That said, it is not uncommon to see this kind of data design, and most relational data stores and the SQL standard itself have made some accommodations.

Trying to use SQL LIKE to accomplish your matching will result in a long list of LIKE and OR statements.  Depending on your backend data store, you can use expanded pattern matching through LIKE or SIMILAR.  The not-so-good news is that expanded pattern matching is very implementation/product specific, so portability suffers.

If you are working with file geodatabases, see if the following works:

-- SIMILAR TO syntax here only works correctly in 
-- ArcGIS Desktop 10.6 or greater and ArcGIS Pro 2.0 or greater
field_name SIMILAR TO '(A1[ ,]%|%[ ,]A1[ ,]%|%[ ,]A1)'‍‍‍‍  

Looking closely at your data, the matching is made more difficult by inconsistent use of spaces.  For some records, there is a space after a comma but not all the time.  My SQL above assumes there may or may not be a single space or comma before or after the value you are searching.

UPDATE:

The comparable SQL to my above using basic pattern matching with LIKE is:

field_name LIKE 'A1,%' OR
field_name LIKE 'A1 %' OR
field_name LIKE '% A1 %' OR
field_name LIKE '% A1,%' OR
field_name LIKE '%,A1 %' OR
field_name LIKE '%,A1,%' OR
field_name LIKE '% A1' OR
field_name LIKE '%,A1'‍‍‍‍‍‍‍‍

The SQL code using LIKE is very portable, but as you can see, you shouldn't expect much for performance when you chaining together so many condition checks using OR.