Search code examples
google-bigquery

Extract last 16 character from a sentence in BigQuery

I need to extract last 16 characters from a sentence under a title column of a table using bigquery. My table is like this:

title

No Commission inc GST - FY20 H2 Rewards Prospecting - SCC_H2_P25_0620

FB/IG - P25 to 55 - Retageting - SCC_H2_P27_0625

I would like to get the output: SCC_H2_P25_0620

                            SCC_H2_P27_0625

Can anyone please assist.

Solution

  • BigQuery Standard SQL

    SUBSTR(title, LENGTH(title) - 15, 15)

    Above extract last 15 chars from title column

    #standardSQL
WITH test AS (
  SELECT 'No Commission inc GST - FY20 H2 Rewards Prospecting - SCC_H2_P25_0620' title UNION ALL
  SELECT 'FB/IG - P25 to 55 - Retageting - SCC_H2_P27_0625'
)
SELECT SUBSTR(title, LENGTH(title) - 15, 15)
FROM test

    output

    Row f0_  
1   SCC_H2_P25_062   
2   SCC_H2_P27_062