Extract last 16 character from a sentence in BigQuery

Question

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.

Answer 1

Based on Big Query (standard SQL) substring built-in function document, BigQuery Substring section

...    
If position is negative, the function counts from the end of value, with -1 indicating the last character.
...

Can't you do the following?

SUBSTR(title, -16)

Answer 2

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