REPLACE Characters in string filters

Looker Tips & Resources Technical Tips & Tricks
, ,
1

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

1 Like
2

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

3

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

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