topic Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string in Data Management Questions

How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

Anonymous User — Wed, 15 Jun 2016 22:14:42 GMT

I’ve used the Spatial Join geoprocessing tool using the JOIN_ONE_TO_ONE option to join a line feature class (target) and polygon feature class (join). I’ve also used the merge rule of join on an ID field with a comma delimiter. So in my resulting feature class has one feature for each of the inputs but it has a field with a comma delimited list of the IDs for the polygons that each line intersected with (like what is described in this blog). So if line A intersected with polygon 1 and 2 it would have a value of ‘1,2’. Now I want to build a query that returns all the lines that intersected polygon 1. You might think that a SQL statement that uses the LIKE operator would work, but then any polygon with 1 in the ID would also be returned (Like ID = 10 or 100). What I really need is something like the IN operator but with the field values providing the list. But I can’t get that to work. Does anyone have an idea on how to build this query? Ideally I would make this query from the operations dashboard but any examples would do. One more snag. If you are thinking of using a bitmask, I have more than 64 polygon IDs.

Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

DanPatterson_Retired — Wed, 15 Jun 2016 22:56:35 GMT

don't know about the query but here is an example that might work in some situations

>>> a = '10,3,1,2'

>>> str(2) in a.split(',')

True

You can get elaborate and convert the split values to numbers if you want

Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

BlakeTerhune — Sat, 11 Dec 2021 07:36:52 GMT

With SQL, I think the "proper" way to do this would be with regular expressions (regex). Not sure if you need SQL Server or Oracle syntax but you can go on the regex adventure yourself. An uglier/messier way to do this would be to just have three LIKE statements; one for each possible scenario the search string could exist.

where intx_field like '1,%'  -- Beginning
    or intx_field like '%,1,%'  -- Middle
    or intx_field like '%,1'  -- End
    or intx_field = '1'  -- Single

Editied to include where clause for single value; thanks Richard Fairhurst

Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

Anonymous User — Thu, 16 Jun 2016 23:50:35 GMT

I like “%” that solution. Get it? Ah SQL jokes are the best.

You would just need to add a line for each significant digit which in my case is 3. Good idea.

I was thinking regex but wasn’t too excited about that. Thanks for the help.

Tom

Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

RichardFairhurst — Fri, 17 Jun 2016 10:04:09 GMT

You forgot to include:

or intx_field = '1'

Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

JoshuaBixby — Sun, 19 Jun 2016 04:23:10 GMT

If commas are prepended and appended to the field in question, either to the data itself or a DBMS-specific concatenation function, a single LIKE statement of '%,1,%' will work. Using SQL Server and concatenation function:

WHERE CONCAT(',', intx_field, ',') LIKE ('%,1,%')

WHERE ',' + intx_field + ',' LIKE ('%,1,%')

Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

BlakeTerhune — Mon, 20 Jun 2016 18:48:59 GMT

As usual, Joshua Bixby has a more elegant solution.

Re: How to build a query to return the unique occurrences of an ID from a field containing a comma delimited string

JoshuaBixby — Mon, 20 Jun 2016 20:50:37 GMT

Thanks. Overall, the OP's approach is very non-relational, so I don't know how elegant any specific syntax can be. In a relational context, there would be a second table that lists each feature along with each intersecting feature, and a simple SELECT, and possibly a JOIN, would be used to identify the features. Unfortunately, being relational in ArcGIS isn't always straightforward, so practicality wins out.