Replace part of REGEXP row in sqlite3

Question

I installed REGEX support with

apt-get install sqlite3 sqlite3-pcre 

I can now use REGEX in my queries in the bash console, for example

 DB="somedb.db" REGEX_EXTENSION="SELECT load_extension('/usr/lib/sqlite3/pcre.so');" sqlite3 $DB "$REGEX_EXTENSION select * from sometable where name REGEXP '^[az]+$'" 

But how can I update a row using sqlite query using regular expression?

Answer 1

Sqlite does not provide the regex_replace function by default. You need to download it as an extension. Here is how I did it.

Download this C code for extension (icu_replace)

Compile it with

 gcc --shared -fPIC -I sqlite-autoconf-3071100 icu_replace.c -o icu_replace.so 

And in sqlite3 runn after executing the command mentioned above, the icu_replace.so file was launched and created

 SELECT load_extension(' path to icu_replace.so', 'sqlite3_extension_init') from dual; 

After that, you can use a function such as: -

 select regex_replace('\bThe\b',x,'M') from dual;