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: 

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!


Happy wildcard filtering!

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • What if you want to match a given special character? Let's say a hyphen for a serial number?


    Then you can do 

    [column] ilike regexp_replace( [value] , '[-]', '%')
    Reply Like
  • 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.


    Reply Like
Like2 Follow
  • 6 mths agoLast active
  • 2Replies
  • 767Views
  • 3 Following