Extract substring based on delimiter

Looker Q&A Looker
1

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
2

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

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

3

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]

4

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

5

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
6

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

7

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

8

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

9

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.

1 Like
10

That’s great, thanks so much :slight_smile: