case_sensitive (for models)

This page refers to the case_sensitive parameter that is part of a model .

case_sensitive can also be used as part of an Explore, described on the case_sensitive (for Explores) documentation page.

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

Usage

 case_sensitive 
 : 
 yes 

Definition

case_sensitive determines whether filters will be treated as case-sensitive for a given model. All filters related to the model are impacted, including those added in the Explore UI, the Dashboard UI, a filter field , or a measure's filters parameter.

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

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

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

  WHERE 
  
 name 
  
 ILIKE 
  
 'bob' 
 

Most SQL dialects support case_sensitive . However, 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. Dialect support is listed in case_sensitive is not supported by some SQL dialects .

Examples

Make all filters case-sensitive for a model:

 connection: "connection_name"
include: "filename_or_pattern"
case_sensitive: yes
explore: explore_name {...} 

Make all filters not case-sensitive for a model:

 connection: "connection_name"
include: "filename_or_pattern"
case_sensitive: no 

Common challenges

case_sensitive is not supported by some SQL dialects

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:

DataVirtuality provides a semantic data layer connecting to various database dialects. If Looker supports case_sensitive for the underlying dialects, then Looker supports case_sensitive for DataVirtuality connecting to those dialects.

Things to know

You can create a case-sensitive search in MySQL

It is possible to create a case-sensitive search in MySQL without using 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) ;;
}