Sqlite3 syntax for regexp in search and replace

Question

I can use regexp to list hits from Sqlite3 db, but what is the syntax for “searching and replacing” with regexp.

Answer 1

If you are thinking about using backreferences in a replacement string, this is 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 on ordinary string functions like replace(...) and substr(...) (or your own extension functions ). Cannot invoke groups found by the REGEXP statement.

EDIT: Here is a specific example that interprets the numerical stock identifiers following the 'STOCK ID: ' prefix 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+'