The Problem

We have a string in our database that contains a comma. For our example, we will be using the string Santa Cruz, CA.

We want to filter on all records that contain this string. However, Looker treats commas as a way to separate values. This means that writing Santa Cruz, CA in an Explore or a Look filter will return all results that contain either Santa Cruz or CA.

The Solution

The solution is to "escape" the comma so that it is treated as part of the string. There are different ways to do this, depending on the context.

Regular UI Filters

When typing the string into the filter, you can include a backslash (\) character to escape the comma. This tells Looker to treat it as an actual comma. To escape the comma with the backslash, we would write Santa Cruz\, CA.

Matches Advanced Filters

The above trick works for regular string filters, but the rules for the Matches (Advanced) option are a little different. Here, the escape character is a carat (^). So, in this example, we would write Santa Cruz^, CA:

LookML Filters

The carat (^) escape character also works in LookML filters. In a filtered measure, we would use the same syntax.

measure: filtered_count {
  type: count
  filters: [
 city: "Santa Cruz^, CA"
  ]
}

Custom Drilling and Links

When creating custom drills with html and link parameters, you can escape commas in drill values with the filterable_value variable where you might normally use the value variable.

The following link drills to an Explore that will filter the results by the users.city value that is selected:

dimension: city {
    type: string
    sql: ${TABLE}.city ;;
    link: {
        label: "Drill by City"
        url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]={{ value }}"
    }
}
  

If the end user clicks Santa Cruz, CA to drill to the results filtered by this city, the resulting drill-down will return all results that contain either Santa Cruz or CA.

If filterable_value is used instead of value, the comma will be escaped:

dimension: city {
    type: string
    sql: ${TABLE}.city ;;
    link: {
        label: "Drill by City"
        url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]={{ filterable_value }}"
    }
}
  

The resulting drill-down will return all results that contain the entire string value Santa Cruz, CA

If we want to hard code a filter value that contains a comma in a drill-down URL, we can escape the comma by wrapping the value in double-quotes and then escaping them with a forward slash (/):

dimension: city {
    type: string
    sql: ${TABLE}.city;;
    link: {
        label: "Drill by City"
        url: "/explore/model_name/explore_name?fields=users.email,users.id&f[users.city]=\"Santa Cruz, CA\"&sorts=users.email"
    }
}

Check out the Help Center article Custom Drilling using HTML and Link for more ways to create custom drills.