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
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:

Dialect Supported?
Actian Avalanche
Yes
Amazon Athena
Yes
Amazon Aurora MySQL
No
Amazon Redshift
Yes
Amazon Redshift 2.1+
Yes
Amazon Redshift Serverless 2.1+
Yes
Apache Druid
Yes
Apache Druid 0.13+
Yes
Apache Druid 0.18+
Yes
Apache Hive 2.3+
Yes
Apache Hive 3.1.2+
Yes
Apache Spark 3+
Yes
ClickHouse
Yes
Cloudera Impala 3.1+
Yes
Cloudera Impala 3.1+ with Native Driver
Yes
Cloudera Impala with Native Driver
Yes
DataVirtuality
Yes
Databricks
Yes
Denodo 7
Yes
Denodo 8
Yes
Dremio
Yes
Dremio 11+
Yes
Exasol
Yes
Firebolt
Yes
Google BigQuery Legacy SQL
Yes
Google BigQuery Standard SQL
Yes
Google Cloud PostgreSQL
Yes
Google Cloud SQL
No
Google Spanner
Yes
Greenplum
Yes
HyperSQL
No
IBM Netezza
Yes
MariaDB
No
Microsoft Azure PostgreSQL
Yes
Microsoft Azure SQL Database
No
Microsoft Azure Synapse Analytics
No
Microsoft SQL Server 2008+
No
Microsoft SQL Server 2012+
No
Microsoft SQL Server 2016
No
Microsoft SQL Server 2017+
No
MongoBI
Yes
MySQL
No
MySQL 8.0.12+
No
Oracle
Yes
Oracle ADWC
Yes
PostgreSQL 9.5+
Yes
PostgreSQL pre-9.5
Yes
PrestoDB
Yes
PrestoSQL
Yes
SAP HANA
Yes
SAP HANA 2+
Yes
SingleStore
No
SingleStore 7+
No
Snowflake
Yes
Teradata
Yes
Trino
Yes
Vector
Yes
Vertica
Yes

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) ;;
}