REPLACE Characters in string filters

Knowledge Drop

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.                

Comments
usheikh
Observer

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

ddd
Explorer

You need to use single quotes around the find and replace strings:

sql: REPLACE(${TABLE}."id", ‘-’, ‘ ‘);;

Version history
Last update:
‎05-07-2021 09:11 AM
Updated by: