Solved: Extract substring based on delimiter - Page 2

sarahuss · 03-24-2021 05:37 AM

Hi

I want to create three new columns from an existing dimension(called category) which has upto 3 keywords in it, with / in between them. EG

/Hobbies & Leisure/Special Occasions/Holidays & Seasonal Events

How can I do this in LookML? I tried the below:

dimension: cats {
    description: "Top level category"
    type: string
    sql: substring(${category},1,position(${category},"/",)-1;;
}

And there were no LookMl errors but when I go in the explore I get:

The Google BigQuery Standard SQL database encountered an error while running this query.

Query execution failed: - Syntax error: Unexpected ")" at [3:74]

SELECT	substring(categories.category,1,position(categories.category,"/",)-1 AS categories_catsFROM `pulse_tables.categories`     AS categoriesGROUP BY 1ORDER BY 1LIMIT 500

 GROUP BY 1ORDER BY 1LIMIT 500

Dawid

Okay, in this case I would do this:

SPLIT(${category}, "/")[SAFE_OFFSET(1)]

This would give you the second value, because the first will be empty since your string starts with your delimiter.

View solution in original post