How best to query

Question

I have a table in a database that stores a number of widgets, or combination of widgets in a single column. The data in the column is comma seperated so will either contain a single widget or multiples. i.e widget widget1,widget2,widget3 In my Appian interface I can add widgets and write them to the db, in any combination. ie. widget3,widget1,wiget4. I'd like to do a query where I return the rows where my widgets match. 
 The problem is if I use equals and my current case has for example widget1,widget3,widget5 
 then I will only get rows returned that have widget1,widget3,widget5 
 rows that have widget3,widge1,widget5 wont get matched. I tired includes and again it only gives me the exact match.

yeswanththiyarir · Answer

Hi, 
Can you try using find for each of the widget. Get a row say widget3,widge1,widget5 strip it using comma. now lets say you are comparing to widget1,widget3,widget5. first check find("widget3","widget1,widget3,widget5") if this is not zero, check for widget1, do the same for next widgets. if the find is never 0 then they are equal. You can ssave the stripped values in array and loop through them. Create a rule with this functionality for reusability. 
Thanks, 
Yeswanth.

simples533 · Answer

Hi Pauls,

We have limited operators in Query Entity.

Two approaches that you can try here is as below,

1. You can write a store procedure using sql/MySql function toachieve this, write SQL query which return result where row match your input as substring.
2. Or else other way is, you can fetch all data in Appian and than using appian function you can find index of row where your search value match as substring, once you have index, you can use index() function and use its output in other variable.

I would suggests to go with first one.

Hope this help you.

Mike Schmitt · Answer

My approach would be similar to Rahul's code above, though I think certain things would need to be tweaked from that. 
Mainly, the operator would need to be "AND", not "OR" -- since "OR" would find every result with either "Widget1" or "Widget3" in it, whereas using AND would return only those results matching all widget names passed in. 
 
As far as I know the only corner case this might fail on is when widget names are duplicated in the text list in a given row, i.e. "widget3,widget1,widget3,widget5". If you don't care about this sort of result also being included, then you should be good to go.