The code below provides a personal note to filter SQLite database using the regular expression via activating REGEXP function in SQL. Accepted SQL regex patterns are available here. In general, it appears that SQL regex does not accept look-ahead and look-behind (?).
library(DBI)
#> Warning: package 'DBI' was built under R version 4.3.3
library(tidyverse)
library(RSQLite)
#> Warning: package 'RSQLite' was built under R version 4.3.3
con <- DBI::dbConnect(RSQLite::SQLite(), "iris.sqlite")
copy_to(con, iris, "iris", overwrite = TRUE)
df <- tbl(con, "iris")
RSQLite::initRegExp(con)
df |> filter(glue::glue_sql("Species REGEXP '^v[^e]'"))
#> # Source: SQL [?? x 5]
#> # Database: sqlite 3.47.1 [/Users/Primahadi/Documents/shinytest/iris.sqlite]
#> Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> <dbl> <dbl> <dbl> <dbl> <chr>
#> 1 6.3 3.3 6 2.5 virginica
#> 2 5.8 2.7 5.1 1.9 virginica
#> 3 7.1 3 5.9 2.1 virginica
#> 4 6.3 2.9 5.6 1.8 virginica
#> 5 6.5 3 5.8 2.2 virginica
#> 6 7.6 3 6.6 2.1 virginica
#> 7 4.9 2.5 4.5 1.7 virginica
#> 8 7.3 2.9 6.3 1.8 virginica
#> 9 6.7 2.5 5.8 1.8 virginica
#> 10 7.2 3.6 6.1 2.5 virginica
#> # ℹ more rowsCreated on 2025-04-08 with reprex v2.0.2