Write a case-insensitive HubDB equals query for a text column
HubSpot's HubDB get_table_rows equals query parameter for text columns does not support case-insensitive filtering by default. But there's a workaround!
For context, in my project I was working with coupon codes using a HubDB table similar to the following:
| COUPON CODE (Text) | DISCOUNT PERCENTAGE (Number) |
| 10off | 10 |
| 15off | 15 |
| 20off | 20 |
And then using a query parameter in the URL (e.g. ?coupon_code=10off) to get the discount:
{% set coupons = [] %}
{# Get the coupon from the URL's query parameter #}
{% set coupon_param = request.query_dict.coupon_code %}
{% if coupon_param %}
{# This is case sensitive! #}
{% set coupon_queryparam = "&coupon_code=" ~ coupon_param %}
{% set coupons = hubdb_table_rows(123456, coupon_queryparam) %}
{% endif %}
However, because "&coupon_code=" query is case sensitive, this was returning no results if the URL parameter was something like ?coupon_code=10FF
As a workaround to this, instead of using the equals query "&coupon_code=", you can use the icontains query "&coupon_code__icontains=" and it will return a case-insensitive match:
{% set coupons = [] %}
{# Get the coupon from the URL's query parameter #}
{% set coupon_param = request.query_dict.coupon_code %}
{% if coupon_param %}
{# This is case insensitive #}
{% set coupon_queryparam = "&coupon_code__icontains=" ~ coupon_param %}
{% set coupons = hubdb_table_rows(123456, coupon_queryparam) %}
{% endif %}
A Caveat
Becuase you're using "&coupon_code__icontains=", it'll return everything that contains "10off", including other results like "10offtoday" or "get10offnow".
If you have this issue, another workaround is to loop through the results to look for matches. To make sure it's case insensitive, make sure both the search term and the table row value use either the |lower or |upper filters.
{% set coupons = [] %}
{# Get the coupon from the URL's query parameter #}
{% set coupon_param = request.query_dict.coupon_code %}
{% if coupon_param %}
{# This is case insensitive #}
{% set coupon_queryparam = "&coupon_code__icontains=" ~ coupon_param %}
{% set coupons = hubdb_table_rows(123456, coupon_queryparam) %}
{% endif %}
{# Loop through the results #}
{% for coupon in coupons %}
{# This condition only prints out results that match the text exactly #}
{% if coupon.coupon_code|lower == coupon_param|lower %}
<p>{{ coupon.coupon_code }}</p>
{% endif %}
{% endfor %}If your HubDB table has very few rows, perhaps you might just pull all rows without any queryparam and use the condition, but this is a way to minimize the number of rows you're working with.
If you found this helpful, buy me a coffee! ❤️
← Previous Article
Prevent a header from overlapping a scroll/anchor link on HubSpotNext Article →
Overriding HubSpot's new forms styling from theme settings