How to get an /exact/ word match in SQLite3's FTS5?
I'm looking at the FTS5 full text searching module for SQLite3 and have a question about how to limit the search to an exact string.
If I have the following quick database: Code:
PRAGMA foreign_keys=OFF; Code:
sqlite> SELECT rowid,m FROM text WHERE m MATCH ('foof'); |
Untested:
Code:
SELECT rowid,m FROM text WHERE m REGEX '\bfoof\b'; |
Quote:
It's pretty useful but a subset of PostgreSQL or MariaDB. Anyway, the puzzle remains. https://www.sqlite.org/fts5.html |
Quote:
Quote:
|
Quote:
What I'm aiming to do is find a way to search for exact URLs which have been mixed into a lot of text. It is thus important to differentiate between the following: http://localhost/one/two/ http://localhost/one/three/ such that if I search for a non-existing URL such as "http:/localhost/one/" it will return an empty set, but if I search for "http:/localhost/one/three/" then it will return one record. Again, I'll keep fighting Cloudflare here to see if I can get a new example through. |
Here is the try without the trigram tokenizer. I have added a fifth record.
Code:
sqlite> SELECT rowid,m FROM text WHERE m MATCH '"foo/ba"'; What I'm aiming to do is find a way to search for exact URLs which have been mixed into a lot of text. It is thus important to differentiate between the following: http://localhost/one/two/ http://localhost/one/three/ such that if I search for a non-existing URL such as "http:/localhost/one/" it will return an empty set, but if I search for "http:/localhost/one/three/" then it will return one record. Code:
PRAGMA foreign_keys=OFF; |
Literal spaces doesn’t work?
Code:
‘“ foof “‘ |
Nope. Spaces do not work:
Code:
sqlite> SELECT rowid,m FROM text WHERE m MATCH '"foo/bar "'; Code:
sqlite> SELECT rowid,m FROM text WHERE m MATCH '"foo/bar\""'; |
I think you want something along the lines of
Code:
CREATE VIRTUAL TABLE text USING fts5(a, b, |
Quote:
Code:
CREATE VIRTUAL TABLE text USING FTS5( m, tokenize = "unicode61 tokenchars '/:%'" ); |
@OP google says you should install component `sqlite3-pcre`
|
Quote:
http://git.altlinux.org/people/at/pa...lite3-pcre.git Perl-compatible regular expression support for the SQLite is not necessarily the kind of code which needs frequent updates, but on the other hand not having been updated in over 15 years raises questions. |
All times are GMT -5. The time now is 06:18 AM. |