Knowledge Drop

REPLACE Characters in string filters

  • 7 May 2021
  • 1 reply
  • 1552 views

Userlevel 5

Last tested: Jul 30, 2019
 

If you see odd results in a drill's filter string, it may be that the strings content has a character that Looker is trying to escape. 

 

Characters like  -  Looker is trying to escape, so we need to do something to avoid that.

To avoid this, you'll want to replace the character(s) that Looker is trying to escape using something like the REPLACE function. This way both the field and the filter results will have the proper character string for filtering.
 

dimension: name

sql: REPLACE(${TABLE}.name_field, "-", " ")

 

 

This content is subject to limited support.                

 


1 reply

When I did this, I get the following error: ERROR: column "-" does not exist Position: 2710

 

Here is my code:

 

  dimension: id {
    primary_key: yes
    type: string
    sql: CAST(${TABLE}."id" AS varchar);;
  }
  
  dimension: trun_id {
    sql: REPLACE(${TABLE}."id", "-", " ");;
  }
  
 

Reply