[QUESTION] how can I run regex search in my querry

[Rignchen]

In one of my projects I have a search option which currently always uses the LIKE '%...%' search type to get the entries where the user's search is contained in the searched row, and I wanted to add a --regex option to replace the contains with some regex matching

After looking online I saw that sqlite seem to have a REGEXP key which can be used instead of the LIKE to use regex search

I tried in my sqlite3 cli (version 3.48.0) and it totally works no problem

sqlite> select alias, path from aliases where path like '%2%' order by alias;
alias12|path12
alias21|path21
alias22|path22
sqlite> select alias, path from aliases where path regexp '2\d' order by alias;
alias21|path21
alias22|path22

However when I tried using the sqlite crate for my rust code, I get an error no such function: regexp

let mut statement = database.connection.prepare("select alias, path from aliases where path like '%2%' order by alias").unwrap();
let mut expected = vec![
	("alias12", "path12"),
	("alias21", "path21"),
	("alias22", "path22"),
].iter()
	.map(|(a, p)| (a.to_string(), p.to_string()))
	.collect::<Vec<(String, String)>>();
while let Ok(State::Row) = statement.next() {
	assert_eq!(expected.remove(0), (
		statement.read::<String, _>("alias").unwrap(),
		statement.read::<String, _>("path").unwrap(),
	));
}

let mut statement = database.connection.prepare("select alias, path from aliases where path regexp '2\\d' order by alias").unwrap();
let mut expected = vec![
	("alias21", "path21"),
	("alias22", "path22"),
].iter()
	.map(|(a, p)| (a.to_string(), p.to_string()))
	.collect::<Vec<(String, String)>>();
while let Ok(State::Row) = statement.next() {
	assert_eq!(expected.remove(0), (
		statement.read::<String, _>("alias").unwrap(),
		statement.read::<String, _>("path").unwrap(),
	));
}

I tried to look online if there was a trait to enable or a command to run to enable the feature but didn't saw anything about it

---

name	version
crate version	0.36.1
rust edition	2021
cargo version	1.86.0
rustc version	1.86.0
sqlite3 cli	3.48.0

[IvanUkhov]

Hello, it looks like it is provided as an extension:

https://www.sqlite.org/src/file?name=ext/misc/regexp.c&ci=tip

You could try enabling extensions and loading this specific:

https://github.com/stainless-steel/sqlite/blob/main/src/connection.rs#L208-L252

[Rignchen]

Hey, thank you for the answer, I didn't found any documentation or example about Connection.load_extension, only thing I saw was this test

#[cfg(feature = "extension")]
#[test]
fn load_extension() {
    let connection = ok!(Connection::open(":memory:"));
    ok!(connection.enable_extension());
    assert!(connection.load_extension("libsqlitefunctions").is_err());
}

Since it's only with feature extension I added it in my cargo.toml

sqlite = { version = "0.36.1", features = ["extension"] }

On stack overflow I saw someone explaining how to enable regexes, so I tried something similar

self.connection.enable_extension().expect("Failed to enable extension");
self.connection.load_extension("pcre.so").expect("Failed to load pcre extension");

but my code now always fails with Failed to load pcre extension: Error { code: Some(1), message: None }

I tried changing the 2nd line with

self.connection.load_extension("/usr/lib/sqlite3/pcre.so").expect("Failed to load pcre extension");

but it's the same result

[IvanUkhov]

The version of SQLite3 that is compiled by sqlite3-src uses the amalgamation, and the amalgamation includes only a subset of the standard extensions, with regexp not being part of that subset:

https://www.sqlite.org/amalgamation.html

That means that you want to use your own installation of SQLite3. And by default, it should take your system's version if available/discoverable:

https://github.com/stainless-steel/sqlite3-src/blob/main/build.rs#L5

So make sure you have it installed and then find the extension in the direction where it is installed. For instance, does /usr/lib/sqlite3/pcre.so actually exist? Try looking for regexp.so.