Question

Using LIKE operator and wildcards with Parameters

  • 28 June 2018
  • 2 replies
  • 5476 views

Is there a way to use a free-text parameter in a fuzzy “LIKE” statement including wildcards?



For example - I have a simple text string parameter called reason_code_filter that accepts any value:





parameter: reason_code_filter {


type: string


}





And a measure set up to use that parameter as a condition





measure: sum_reason_code {


type: sum


sql: CASE WHEN reason_code LIKE {% parameter reason_code_filter %} THEN 1 ELSE 0 END ;;


}





This works great if a user enters the full exact value of “reason_code” in the filter field, but I’m looking to set it up so that they can just enter part of the string. So I want to change the resulting SQL from





CASE WHEN reason_code LIKE ‘text’ THEN 1 ELSE 0





to





CASE WHEN reason_code LIKE ‘%text%’ THEN 1 ELSE 0





Is this something that’s possible to do? I’ve been playing around with trying to concatenate the wildcards into the sql field of the measure with no luck so far.



Any help is appreciated!



Edit: I should mention that I know I could achieve this by setting the expectation that if a user wants to do a partial match they have to put the wildcards in the filter field themselves, but looking to see if there’s a way to have it always run that way through the LookML.


2 replies

Hi Josh- depending on your use case a templated filter might be useful since it’s a bit more flexible, see the difference between parameters and templated filters in this doc. For a templated filter, it could look something like:



type: sum

sql: CASE WHEN {% condition reason_code_filter %} reason_code {% endcondition %} THEN 1 ELSE 0 END ;;

}



If you decide you need to go the parameters route, it might be useful to try type: unquoted documented here: https://docs.looker.com/reference/field-reference/dimension-type-reference#unquoted. You could define the parameter by allowing only certain values, but with wildcards for the users to still have the flexibility. For parameter type: unquoted, something like:



type: sum

sql: CASE WHEN reason_code LIKE "%{% parameter reason_code_filter %}%" THEN 1 ELSE 0 END ;;

}

Aha - templated filters are exactly what I was looking for. Thanks a bunch Desiree!

Reply