Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

replace a part of a string with REGEXP in sqlite3

Tags:

regex

sqlite

pcre

I installed REGEX support with

apt-get install sqlite3 sqlite3-pcre

now I can use REGEX in my queries on the bash console like

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

But how can I update a string with an sqlite query using regex?

like image 847
rubo77 Avatar asked Aug 10 '16 15:08

rubo77


1 Answers

Sqlite by default does not provide regex_replace function. You need to load it as an extension. Here is how i managed to do it.

Download this C code for the extension (icu_replace)

Compile it using

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

And in sqlite3 runn following command post above mentioned command has run and create a file icu_replace.so

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

After this you will be able to use such a function as :-

select regex_replace('\bThe\b',x,'M') from dual;
like image 166
Vishal Tyagi Avatar answered Sep 19 '22 14:09

Vishal Tyagi