I have a use case where an input will contain a string or a comma seperated list of strings.
I need to then filter an entity where any item in the input is found in a "description" column.
For example, Input = "dog, cat, apple, banana, blue, red"
Entity records:
So the query would return records 1,3 and 5.
I am using an advanced SQL query, but operations like CONTAINS does not work due to the entity not being full-text indexed.
SELECT {Data}.* from {Data} CROSS APPLY STRING_SPLIT(@Filter, ',') AS Split WHERE {Data}.[Description] LIKE '%' + LTRIM(RTRIM(Split.value)) + '%';
This works for SQL Server.
Hi,
Not sure about a solution using SQL, but it can be achieved pretty easily iterating both lists and check for each string if it's contained in the main string. Something like the following:
The condition in ListIndexOf should be Index(Value,StringList.Current)<>-1 where Value is the description and the current value is the iterated string.
It's not the best solution performance wise, but it works.
Regards
Thank you. I guess I can use that as a workaround and process the results from other filter fields first to reduce the amount of records I'm searching through. Alternatively I could give the user 5 or so inputs for keywords and then make it filter on each...
Thats perfect. Thank you.