sqlite3 syntax for regexp in "search and replace"

Question

I can use regexp to list hits from a Sqlite3 db, but what is the syntax for a "search and replace" using regexp.

Answer 1

If you are thinking of using backreferences in the replacement string, that's not possible, AFAIK. You do an UPDATE, as follows:

UPDATE foo
   SET bar = <some expr including baz>
 WHERE baz REGEXP <regex>

But the assigned expression will have to rely conventional string functions like replace(...) and substr(...) (or your own extension functions). There is no way to invoke groups found by the REGEXP operator.

EDIT: Here's a concrete example that interprets numeric stock IDs following the prefix 'STOCK ID: ' in the item_key column as stock numbers:

UPDATE staff
   SET stock_number = CAST(substr(item_key, 11) AS INTEGER)
 WHERE item_key REGEXP '^STOCK ID: \d+'