A More Robust LIKE/SIMILAR TO/Wildcard Filter At The Dashboard Level With A Matching Expression Filter!

[intapiuser]

You may have seen our earlier post on how to create a LIKE wildcard filter by concatenating the wildcard operator (%) onto a filter value. 

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!