SQL Query or Aggregate combining LIKE and IN

Hi,

I need  a query or aggregate using IN (@list ) where @list is a multi-value list and use the values to query on column that has partial match of the values (LIKE %values%)


Something like:

Table.Column LIKE '%' +  IN (@list ) + '%'


Sample values:

@List = "Men", "Ladies", "Boys", "Girls"

@List = "Men","Boys"

etc...


Table.Column = "Men , Ladies"

Table.Column = "Girls , Boys"

Table.Column = "Men , Boys"

Table.Column = "Men, Ladies, Boys, Girls"

etc..


Is this even possible?


Thank you.


Regards,

-Edwin

Hi Edwin,

I think that is not possible. You have to make Table.Column like all the values in the @list. So, something like Table.Column like "%Men" or Table.Column like "%Ladies%" etc

Regards


Hi,

Aggregates: Not possible.

SQL Tool. Use a Query Parameter to receive the list, with the property Expand Inline set to True/Yes. Remember to sanitize the values to guarantee they are literals, before creating the list of the IN.

Also, remember the List must contain at least ONE value, so you may want to construct something like "OR {Entity}.[Value] IN (value, value, value)" to guarantee you don't break the query if there are no values.

Hope this helps.

Cheers.

P.S. The IN clause does not accept the "LIKE" clause. If you really need it, you need to create the parameter as Bruno told, but remember the OR/AND or even the WHERE if necessary (in case this is the only condition.

"WHERE {Entity}.[Column] like "%Men" or {Entity}.[Column] like "%Ladies%"

Et.

Cheers

You might also want to add a leading and trailing comma in case you are going to use both 'men' and 'women'. In that case you can search if ",men," occurs in your list, so that "women' will not match.

Having said that, couldn't you use a separate (static?) entity for those values (Men, Ladies, Girls, Boys, etc.) and link them to the main entity using an intermediate table?

Hi! 

I have seen here in the Community another option but couldn't find it now. It goes something like this



Use aggregates; 

Create a string with the list  -> @List = ",Men,Ladies,Boys,Girls,"

The filter would be : 

@List LIKE ",%"+ TableColumn + "%,"


I didn't test it, but liked the idea 

Hope this help you

Graça

Hello everyone,

Thank you for your replies.

The table column (text field) can have the following values:

"Men, Ladies, Boys, Girls," or "Men," or "Men, Ladies,"

The List may have the following.

"Men":"Ladies":"Boys":"Girls" or "Men" or "Men":"Ladies"


Currently, I have created text1 to text10 as temporary variables to store the List and use them in aggregate

If(text1 = "", False, TableColumn like "%" + text1 + "%") or If(text2 = "", False, TableColumn like "%" + text2 + "%") or If(text3 = "", False, TableColumn like "%" + text3 + "%") or If(text4 = "", False, TableColumn like "%" + text4 + "%") or If(text5 = "", False, TableColumn like "%" + text5 + "%") so on...


Problem is that List can have max 35 entries, and can grow in the future.

Thank you.

Regards,

-Edwin