Updating SQL query WHERE clause with LIKE % operator

[mnmemilymnm]

I am trying to build an app where someone can use a list box to select multiple parameters for a WHERE clause of a SQL query. I am familiar with using the Dynamic Input tool to update a WHERE IN clause, but WHERE IN doesn't work with LIKE, so I need to update code similar to the following:

WHERE [Table].[Column] LIKE ABCD% or [Table].[Column] LIKE EFGH% ...et cetera

What I want is for the user to select from a list box the conditions for their query, e.g. the list box includes ABCD, EFGH, IJKL, and MNOP, and the user selects which ones they want. (Let's say they choose ABCD and EFGH.) Then instead of feeding directly into an Action tool, the workflow takes the selections from the list box, does a crosstab, creates a string out of the fields (e.g. "[Table].[Column]. LIKE "+[Field1]+"% or "+"[Table].[Column]. LIKE "+[Field2]+"%", which yields [Table].[Column] LIKE ABCD% or [Table].[Column] LIKE EFGH%), and then uses the Action tool to replace a specific string in the SQL query (the WHERE clause to be precise).

I am trying to take this approach, but it seems like I can't take the selections from the list box and create a string with the LIKE operator that then feeds into the SQL query.

Does anyone have any strategies for customizing a SQL query WHERE clause with multiple LIKE operators?

[AkimasaKajitani · Accepted solution]

Hi @mnmemilymnm ,

I tried to rewrite SQL by using the dynamic rename tool, but I can't do it.

So I think it is easy to rewrite the SQL of the Input Data tool directly.

I set the List Box tool as is follows.

Start text is as follows.

WHERE (SampleSuperStore_en.Category Like '

Separator is as follows.

%') or (SampleSuperStore_en.Category Like '

End text is as follow.

%')

Action tool setting is as follows.

The setting of Input tool' SQL is as follows. 

select SampleSuperStore_en.* 
from SampleSuperStore_en 
where SampleSuperStore_en.Category Like 'Office%'

This is how I rewrite the where clause.

[mnmemilymnm]

@AkimasaKajitani  Your solution works perfectly! Thank you so much. Before seeing your solution, I ended up reverting to using the List Box to modify a WHERE IN clause and just resigning myself to not being able to use the LIKE operator, but your solution is such a creative combination of both!