case_sensitive (for Explores)

This page refers to the case_sensitive parameter that is part of an Explore.

case_sensitive can also be used as part of a model, described on the case_sensitive (for models) parameter documentation page.

case_sensitive can also be used as part of a dimension, described on the case_sensitive (for fields) parameter documentation page.

Usage

explore: explore_name {
  case_sensitive: yes
}

Hierarchy Model File explore case_sensitive

Default Value yes, if the database dialect supports the parameter Accepts A Boolean (yes or no)

Definition

case_sensitive determines whether or not filters will be treated as case-sensitive on a given Explore. All filters related to the Explore are affected, including those added in the Explore UI, Dashboard UI, and filters parameter.

By default, case_sensitivity is on and filters are case-sensitive. However, some dialects do not support this parameter, as described in the case_sensitive is not supported by some SQL dialects section on this page.

case_sensitive works by adjusting the WHERE clause of the SQL that Looker generates. When case_sensitive is on, filters are expressed with = or LIKE, such as:

WHERE name = 'bob'
WHERE name LIKE '%bob%'

When case_sensitive is off, filters are expressed with ILIKE (or equivalent), such as:

WHERE name ILIKE 'bob'

Examples

Make all filters case-sensitive for the Product Explore:

explore: product {
  case_sensitive: yes
}

Make all filters not case-sensitive for the Customer Explore:

explore: customer {
  case_sensitive: no
}

Common challenges

case_sensitive is not supported by some SQL dialects

By default, case_sensitivity is on and filters are case-sensitive. If your SQL dialect doesn't support the case_sensitive parameter, case-sensitivity will vary according to your database setup, which will usually not be case-sensitive.

For Looker to support case_sensitive in your Looker project, your database dialect must also support it. The following table shows which dialects support case_sensitive in the latest release of Looker:

Things to know

You can create a case-sensitive search in MySQL

It is possible to create a case-sensitive search in MySQL, even though MySQL does not support the case_sensitive parameter. In MySQL, certain data types — called binary strings — store text as a series of numbers. The capitalization of the text makes a difference in the numbers that are used. Therefore, if you convert your text to a binary string, you can make searches that are case-sensitive. For example:

dimension: will_NOT_be_case_sensitive {
  sql: ${TABLE}.something ;;
}

dimension: will_be_case_sensitive {
  sql: CAST(${TABLE}.something AS BINARY) ;;
}