A more robust LIKE/SIMILAR TO/wildcard filter at the dashboard level with a Matching Expression filter!
I previously wrote a post on how to create a LIKE wildcard filter by concatenating the wildcard operator (%) onto a filter value.
One of my 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:
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!
Happy wildcard filtering!
This got me VERY excited about being able to fix the problem where I can't use an email address as the filter string. I did this:
disc_code ilike regexp_replace('%' || [value] || '%', '[@]', '%')
Unfortunately, when I try a search string like "p@cklane" what the query receives is:
WHERE (disc_code ilike regexp_replace('%' || 'pcklane' || '%', '[@]', '%'))
and therefore matches no rows. It appears that the @ is being stripped even before the regexp can process it.
Is there ANY way around this? It's a real problem that we can't search using a customer's email address as a filter.