Could you please help using regexp_extract digits from the following example lines:
Into:
fyi: long-multiple space is around 40-50 spaces
Thank you
For lines that terminate with 1-or-more spaces, and 'long-multiple-space' is 40-50 spaces.
Here's some different match expressions, depending on your needs...
Match: ^.* {40,50}(\d+) +$
Replace: Group1
Match: ^[A-Z\d]+(?: [A-Z]+){1,2} {40,50}(\d+) +$
Replace: Group1
With regexp_extract, I think the syntax looks more like...
regexp_extract(filename,'^.* {40,50}(\d+) +$', 1)
regexp_extract(filename,'^[A-Z\d]+(?: [A-Z]+){1,2} {40,50}(\d+) +$', 1)
The {1,2} is what matches either 1-or-2 names (like OLLA and JENNY on Line1).
Use {1,9} to match anywhere from 1-9 of those space-separated names.
(same thing with {40,50} for the 'long-multiple-spaces')
Consider below approach
select array_reverse(split(trim(col_name), ' '))[offset(0)]
from your_table
if applied to sample data in your question - output is
you can transform your string to simple space separated values then extract numerical values using regexp_extract on top of that resultant string.
select REGEXP_REPLACE(REGEXP_REPLACE("11BARIIOTA0292DEBORAH OLLA JENNY 0000001242202173171"," ",""),'[^0-9 ]','');
