Question about SQL Parameters and LIKE statements

Get Help
1

Hi there,

Loving Metabase so far. I’ve been experimenting with using SQL Parameters and in general they work great, but so far I haven’t found a good way to use them for LIKE statements.

If you do:

[[ and category = {{ brand_category }} ]]

everything works great. However trying to do:

[[ and category ilike = ‘%{{brand_category}}%’ ]]

Fails with a “Invalid parameter index: 1.”, seemingly because the parameter is INSIDE the string. Is there a way around this? I can do ilike {{brand_category}} but then the user has to put the % in the filter parameter text box which isn’t intuitive.

I got it working by concatenating the % with the SQL parameter but this seems clunky:

[[ and ti_category ilike (’%’ || {{brand_category}} || ‘%’) ]]

Is there a better way to do this?

I’m using metabase 0.29.3, with RedShift as the database.

2

Hi,
not sure if RedShift is a mySQL database and therefore not sure if the syntax is completely similar but for me this syntax works:
categories_list LIKE CONCAT (’%’,{{cat}},’%’)

At least for mySQL you have to use concat with the “%”.
Hope that helps!
Cheers, Eva

3

For reference, from this,

"Amazon Redshift is based on PostgreSQL 8.0.2"

This is the reference link for the concatenation operator.