Hi,
One possible way could be to combine this with FTS4 (with parenthesis
support enabled) and a LIKE clause:
SELECT substr(path, 4+instr(substr(path,4),'/‚)) as relativepath FROM
table WHERE table MATCH "path:ab AND path:cd“ AND path LIKE "ab/cd%“
How it works:
- The match clause efficiently filters for all records containing ab and
cd (the / is treated as a separator for words by the fulltext tokenizer).
- The records returned by match mean that the words we searched for
occurred SOMEWHERE within the record. Since you’re only interested those
beginning with "ab/cd“ we must use another LIKE. But this is not a
peformance issue, because you effectively only apply the LIKE to the
records that MATCH returned
- The substr(…) is just applied to those records returned
It should be fairly efficient this way. A limitation is however that MATCH
doesn’t like special characters. If you have for example hyphens or spaces
in your path names. But even then it would work, you’d just need to build
an array of words, replacing any special chars with spaces. For example:
Path-_1/path%2/path3_
When we build an array by replacing all non-alphanumeric chars with empty
spaces we get:
"Path 1 path 2 path3 "
Notice we have two spaces in path 1 for „-_“. Normalize that as well,
removing duplicate as well as leading / trailing spaces:
"Path 1 path 2 path3“
Make an array of search words, breaking at the spaces:
[Path, 1, path, 2, path3]
Create your match statement with that:
MATCH "pathcolumn:Path AND pathcolumn:1, AND pathcolumn:path AND
pathcolumn:2 AND pathcolumn:path3“
Create your like statement, appending the % at the end, and normalizing
the input string (to make % inside your text not recognized by like but
treated as test really):
char *zSQL = sqlite3_mprintf("Path-_1/path%2/path3_", zText);
This will give some properly escaped string. Then use that string for your
LIKE statement, which will look something like (just quick example, not
sure if entirely correct)
LIKE „Path-_1/path\%2/path3_"
Regards
Ben
Am 26.10.14 13:27 schrieb "Baruch Burstein" unter <bmburst...@gmail.com>:
>Hi!
>
>I have a column which represents a file path:
>
>ab/cd/gf
>ab/qw/ert
>ab/fgrd/ert
>ab/foo/bar/fgr
>ab/bar/foo/foobar/etc
>ab/etc/d
>etc...
>
>I happen to know in my case that the first part of the path is a certain
>fixed string ('ab' in the above example). I need to get the path with the
>first 2 parts stripped off. Currently I am doing:
>
> substr(path, 4+instr(substr(path,4),'/'))
>
>But that seems long and probably inefficient.
>What is the best/simplest way to find the second occurrence of the '/' in
>a
>string?
>
>Also, a suggestion for an SQLite improvement: The builtin function instr()
>should have another form that takes 3 arguments, with the 3rd being either
>an offset from where to start the search, or which occurrence to search
>for
>(1st, 2nd, etc.)
>
>--
>˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
