Reputation: 65
I have this data below which is Quarters or Years. How do I in SQL legacy rewrite it so it can show?
RAW Data:
Goal (Result needed):
NOTE: I would like the sql to also do the following:
I tried CHARINDEX My error message states "2.10 - 2.23: Unrecognized function charindex"
Thanks for looking into this.
Upvotes: 1
Views: 381
Reputation: 173210
Below is for BigQuery Standard SQL
select line,
format('FY%s_%s%s',
split(parts[offset(1)], '/')[offset(0)],
parts[offset(0)],
ifnull('_' || parts[safe_offset(2)], '')
) as result
from `project.dataset.table`,
unnest([struct(split(line, '_') as parts)])
if applied to sample data from your question - output is
Upvotes: 1