Whats the most efficient way to search for a sub string in SQLite?
I'm looking at the LIKE operator.
Do I have the right idea? Has this worked well for you?
http://www.sqlite.org/lang_expr.html
Thank You.
+2
A:
Yepper, use Like. Select id from sometable where name like '%omm%' would return any row that had 'omm' anywhere in the name column.
GrandmasterB
2010-09-08 20:51:16
@GrandmasterB: what if the string actually contained the "%" character. What does SQLite do in that case?
Tommy
2010-09-08 21:18:07
There's an ESCAPE keyword that lets you define an escape character so that you can query on % or _. See: http://www.sqlite.org/lang_expr.html So you'd do something like "select id from mytable where name like '%somename\%%' escape '\'
GrandmasterB
2010-09-08 21:24:27
+4
A:
You can use LIKE, but it gets really slow if the pattern you're searching for starts with '%' -- i.e., if the substring you're looking for isn't necessarily at the beginning of the field.
If you need to do such searches, consider using FTS3, which makes full-text searching considerably more efficient.
Jerry Coffin
2010-09-08 21:04:34
@Jerry Coffin: Interesting, is there a lot of overhead to use FTS3? Can it be used with the SQLite API in C?
Tommy
2010-09-08 21:09:11
@Tommy: I'm not sure what overhead would qualify as a lot from your perspective. Yes, it can be used from the C API.
Jerry Coffin
2010-09-08 21:44:57
The FTS extension is included in the SQLite distribution. You just need to read the docs and turn it on at build time. Note that it will build a non-negligibly sized index with which it works its magic...
RBerteig
2010-09-09 07:27:44