Str_extract alternative for dplyr/sql query

I'm trying to mutate a column in a large dataframe by using str_extract or equivelently stri_extract_all_regex with the stringi package. Problem is one column I need to keep has entries with 1000+ words in them so the code is ridiculously slow if I work with a collected frame (~5 minute queries). Since those functions are not translatable to sql directly, running the code while the table is uncollected spits out errors forcing me to collect which takes forever. I want to know if there is an alternative function to use to extract substrings (based on regex patterns) that is directly compatible with sql (allowing me to keep the data uncollected reducing running time)

library(DBI)
library(dplyr)
library(dbplyr)
library(dfcr)
library(stringr)
library(lubridate)
library(timeDate)
library(plotly)
library(openxlsx)
library(bit64)
library(cld3)
library(stringi)

doConn("odbc")
sc <- dbConnect(odbc::odbc(),"Impala")
options(scipen = 999)
currentdate <- Sys.Date() 

DF1 <- sc %>% tbl(tblsan("rawframe1")) %>%
  filter(between (as.Date(createtime), as.Date("2022-11-01"), as.Date(currentdate))) %>%
  mutate(id = as.character(id),
         userid = as.character(userid)) %>%
  select(id, userid, body, createtime)
  
 
DF2 <- DF1 %>%
  left_join(., sc %>% tbl(tblsan("rawframe2")) %>%
              filter(as_of_date == "2023-07-24") %>%
              collect() %>% /*IDEALLY NO COLLECT HERE*/
              mutate(user_id = stri_extract_all_regex(raw_user_key,"(?<=:)([0-9]*)")) %>%
              select(user_id,name,login) %>%
              distinct(), 
            by = c('userid' = 'user_id'))

Example Entries of the DataFrames

DF1

RAWTABLE2

#in my code, im trying to extract everything after the colon with stri_extract_all_regex.
Again, goal is to not collect the left_joined df (rawframe2) since ill then have to collect df1 which will take forever

Disclaimer: I'm not specifically well versed in Impala's SQL dialect nor do I have access to an instance to fully test this.

Any function for which dbplyr doesn't offer an automatic translation, you can write in custom SQL. From the Impala documentation, it seems like REGEXP_EXTRACT would be the closest built-in function.

DF2 <- DF1 %>%
  left_join(., sc %>% tbl(tblsan("rawframe2")) %>%
              filter(as_of_date == "2023-07-24") %>%
              mutate(user_id = sql("REGEXP_EXTRACT(raw_user_key,'(?<=:)([0-9]*)', 0)")) %>%
              select(user_id,name,login) %>%
              distinct(), 
            by = c('userid' = 'user_id'))