Extract substring based on delimiter

[sarahuss]

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]

It looks to me like you’re missing a parenthesis:

    sql: substring(${category},1,position(${category},"/",)-1);;

[sarahuss]

Thanks Dawid,

Good spot. Unfortunately when I added that in I still got the same error on my explore. Here is the code:

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

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

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

[Dawid]

What dialect do you use? You also have a comma after the last argument of the position

position(${category}, “/”)

vs

position(${category}, “/”, )  ← this one has a comma making the parenthesis unexpected

[sarahuss]

Ah yes, still doesn’t like it though!

I’m using Google BigQuery Standard SQ:

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

Query execution failed: - Function not found: position at [3:41]

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

[sarahuss]

Here is the LookML again:

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

I changed the dimension name to check something but it’s supposed to be the same as the abover ‘cats’ dimension

[Dawid]

BigQuery doesn’t have a function POSITION. You can perhaps use STRPOS but depending on what your data looks like, you might be able to use SPLIT, or even REGEX functions

[sarahuss]

Thanks Dawid. I’ll look into alternatives. The data is like below:

So I just want to extract words between the / delimeter

I tried using STRPOS instead of position. It compiled but gave no results

[Dawid · Accepted solution]

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.

[sarahuss]

That’s great, thanks so much 🙂