Create new column with everything is between third - and forth - of another column

user_11122513 0
Viewed 91

I need to create a new column based on the column col, butextracting everything between the third - and the forth -. Examples:
ABC-123-aaa-INEEDTHIS-2000-BBB-123-CCC
111-AAAAA-bb-INEEDTHIS-BB-435-A

How can I do this with Big Query?
I'm trying something like this:

SELECT REGEXP_EXTRACT(col, r'\w\w[^\d]\d\d') as newcol from mytable

I would like to understand too the regex behind this solution, if possible.

google-bigquery sql regex
1 Answers

Below is for BigQuery Standard SQL

The simplest way is to use SPLIT function as in below example

SELECT SPLIT(col, '-')[SAFE_OFFSET(3)] AS newcol

If for some reason you want to go with regular expression - you can use REGEXP_EXTRACT as in below example

REGEXP_EXTRACT(col, r'(?:[^-]*-){3}([^-]+)') AS newcol

You can test, play with both above approaches using sample data from your question as in example below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'ABC-123-aaa-INEEDTHIS-2000-BBB-123-CCC' col UNION ALL
  SELECT '111-AAAAA-bb-INEEDTHIS-BB-435-A'
)
SELECT 
  col, 
  SPLIT(col, '-')[SAFE_OFFSET(3)] AS newcol_with_split,
  REGEXP_EXTRACT(col, r'(?:[^-]*-){3}([^-]+)') AS newcol_with_regexp
FROM `project.dataset.table`

with output

Row col                                     newcol_with_split   newcol_with_regexp   
1   ABC-123-aaa-INEEDTHIS-2000-BBB-123-CCC  INEEDTHIS           INEEDTHIS    
2   111-AAAAA-bb-INEEDTHIS-BB-435-A         INEEDTHIS           INEEDTHIS

Brief explanation for regexp

  1. [^-]*- captures entries like ABC-, 123-, aaa- etc.
  2. (?:[^-]*-) makes sure those entries will not be extracted
  3. (?:[^-]*-){3} makes sure first three such entries will be skipped
  4. Finally, ([^-]+) extracts target fragment till next -
user_52219440
Related