Bigquery SQL extract text left of the string

Question

I have this data below which is Quarters or Years. How do I in SQL legacy rewrite it so it can show?

RAW Data:

Q2M4_20/21
Q3M2_20/21
Q1M4_18/19
Q1_18/19_Actuals
Q1_18/19_Budgets

Goal (Result needed):

FY20_Q2M4
FY20_Q3M2
FY18_Q1M4
FY18_Q1_Actuals
FY18_Q1_Budget

NOTE: I would like the sql to also do the following:

Reorganize from QQ_YY to YY_QQ
Remove "/##" the forward slash and the two digits after
Add text "FY" before the "YY_QQ

I tried CHARINDEX My error message states "2.10 - 2.23: Unrecognized function charindex"

Thanks for looking into this.

Mikhail Berlyant · Accepted Answer · 2021-01-08 22:04:07Z

1

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

answered Jan 8, 2021 at 22:04

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bigquery SQL extract text left of the string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related