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

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   

Answer 2

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 3

Not too sure how your full string looks like. If it looks like that

'No Commission inc GST - FY20 H2 Rewards Prospecting - SCC_H2_P25_0620 FB/IG - P25 to 55 - Retageting - SCC_H2_P27_0625'

Then I'd suggest you to use regex:

SELECT REGEXP_EXTRACT_ALL('No Commission inc GST - FY20 H2 Rewards Prospecting - SCC_H2_P25_0620 FB/IG - P25 to 55 - Retageting - SCC_H2_P27_0625', "- (SCC[^;]*) FB/IG") AS output;

output SCC_H2_P25_0620

Or you can use a SUBSTR function if you know for sure that your string will always be the same length:

SELECT SUBSTR('No Commission inc GST - FY20 H2 Rewards Prospecting - SCC_H2_P25_0620 FB/IG - P25 to 55 - Retageting - SCC_H2_P27_0625', 55, 15) AS output;

output SCC_H2_P25_0620

cheers