QuicKick
QuicKick

Reputation: 65

Bigquery SQL extract text left of the string

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

RAW Data:

  1. Q2M4_20/21
  2. Q3M2_20/21
  3. Q1M4_18/19
  4. Q1_18/19_Actuals
  5. Q1_18/19_Budgets

Goal (Result needed):

  1. FY20_Q2M4
  2. FY20_Q3M2
  3. FY18_Q1M4
  4. FY18_Q1_Actuals
  5. FY18_Q1_Budget

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

  1. Reorganize from QQ_YY to YY_QQ
  2. Remove "/##" the forward slash and the two digits after
  3. 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.

Upvotes: 1

Views: 381

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

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

enter image description here

Upvotes: 1

Related Questions