Updating a Sqlite Table with Regular Expressions

Today, I had a table with some data, and wanted to clean that data up by running a regular expression search and replace on the values. Sqlite3 supports this via loadable modules. For example, on Ubuntu, there’s a sqlite3-pcre package that can be installed for this. On macOS, I didn’t find a similar package in homebrew after a quick look.

After some searching, however, I came across the sqlite-regex project, a regular expression extension for Sqlite written in Rust. I downloaded the sqlite-regex-v<version>-loadable-macos-<arch>.tar.gz file from their releases, un-tarred it, and then right clicked the .dylib and chose open, and then said open in the various macOS security dialogs.

Afterwards, I tried to load the module using .load regex0, but it did not work. This turns out to be because the pre-bundled Sqlite with macOS does not have loading enabled by default. Installed sqlite3 from Homebrew and ran it and was good to go (run it from /opt/homebrew/opt/sqlite/bin/sqlite3):

.load regex0
update mytable set text=regex_replace_all('[^\p{Alpha}]', text, '');

The documentation for sqlite-regex has more functions and examples, but this is certainly useful! As an aside, I found that mixing between running sqlite3 from macOS and the one from Homebrew messes up the ~/.sqlite_history file, so it’s best to stick to one of those instead of switching between both.