need to find the first to last word in a semicolon separated string

[jhalfon]

Hi, I have a string dim that I need to find the first to last word and it’s semicolon separated. Example: List = “once;twice;threetimes;ninetimes;eight;finallist;” I need to return : “eight”

source is BigQuery and the field always ends with a “;”

[mprogano]

I’d recommend writing some regex and use REGEXP_EXTRACT in bigquery

[yuriy]

I think you could use liquid html to

1. split the result by semicolon and
2. Assign a variable with the size of that list `{% assign my_array = “once;twice;threetimes;ninetimes;eight;finallist;” | split: “;” %}
3. get that element from array with size variable, e.g. {my_array[my_array.size-1]}.

[fabio1]

Assuming you want to do it in-model/in-BQ, I’d try something like: ARRAY_REVERSE(SPLIT(${your_string_dimension},";"))[SAFE_OFFSET(2)]

I’ve used an offset of 2 because the last string returned by SPLIT would always be the empty string after the final ;

[jhalfon]

This worked nicely. Thank you!