One of our engineers suggested a better method - using a
Matching Expression Filter type instead. This way, the wildcard feature is built-in to the filter and occurs at the filter level, rather than having to call it into the SQL of the individual charts! Just recently, I worked with a customer who had cleverly implemented this kind of filter, and was looking for a method to allow user-entered matches to multi-word values.
It's important to note that concatenating the wildcards won't work when spaces are included - i.e. if the user enters 'Empire State Building', concatenating wildcards to the ends will match for '%Empire State Building%' - however, this won't match the value 'Empire State Building'. We need a way to ignore whitespaces to allow our users to wildcard match multiple words.
How do you implement this? In this case, you'll perform the same step as the previous post - create a filter with "allow users to enter filter values". This time, you'll also select 'Match an expression for this filter'.
Inside of the matching expression box, you'll use the SQL:
regexp_replace('%' || [column] || '%', '[\\s]', '%') ilike ('%' || [value] || '%')
now in the sql (let's assume you called this filter 'my_wildcard_filter') you can do:
WHERE [column_name=my_wildcard_filter]
and this will automatically apply the wildcard searching, and allows the user to enter spaces.
How does it work? The regexp_replace() function above will replace ANY whitespace character [\\s] with a wildcard symbol '%' therefore allowing the end user to type in spaces, and still getting a match onto the relevant data!
