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.
-
@pmg: Using the SQLite API in C. – T.T.T. Sep 8 '10 at 21:09
Add a comment
|
Yepper, use Like. Select id from sometable where name like '%omm%' would return any row that had 'omm' anywhere in the name column.
answered Sep 8 '10 at 20:51
-
what if the string actually contained the "%" character. What does SQLite do in that case? – T.T.T. Sep 8 '10 at 21:18
-
4There's an ESCAPE keyword that lets you define an escape character so that you can query on % or _. See: sqlite.org/lang_expr.html So you'd do something like "select id from mytable where name like '%somename\%%' escape '\' – GrandmasterB Sep 8 '10 at 21:24
-
1
-
does it return true if the string starts or ends with 'omm' @GrandmasterB – Ahmed Rajab Sep 23 '17 at 18:06
-
@AhmadRajab in that example, it returns all rows that have "omm" anywhere in the string. – GrandmasterB Sep 24 '17 at 5:34
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.
answered Sep 8 '10 at 21:04
-
Interesting, is there a lot of overhead to use FTS3? Can it be used with the SQLite API in C? – T.T.T. Sep 8 '10 at 21:09
-
@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 Sep 8 '10 at 21:44
-
1The 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 Sep 9 '10 at 7:27
-
I found this slightly ambiguous. By
If you need to do such searchesdo you mean pattern starts at the beginning of the field or does not start at the beginning? Because fts in SQLite doesn't support suffix matches. – Jason Mar 13 '19 at 0:59
Years have passed since the question was asked and answered and in 2012 SQLite version 3.7.15 introduced a function instr( string, substring) - returns the location of a substring in a string, 0 if not found. (https://www.techonthenet.com/sqlite/functions/instr.php)
sqlite> SELECT instr('TechOnTheNet.com', 'T');
Result: 1
I have not benchmarked against LIKE, but IMHO could be faster.
answered Jan 6 '19 at 7:52
