How to search for a substring in SQLite?

Question

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.

Solution 1

Yes, use Like. A query such as:

Select id from sometable where name like '%abc%'

would return any row that contained "abc" anywhere in the name column.

If the pattern you are looking for happens to contain the % or _ character, you can use the ESCAPE keyword to define an escape character to include that special character in the expression. To look for the string "somename%" (including the %), it'd look something like:

select id from mytable where name like '%somename\%%' escape '\' 

See: SQLite Language Expressions

Solution 2

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.

Solution 3

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.

Comments

[T.T.T.]

Interesting, is there a lot of overhead to use FTS3? Can it be used with the SQLite API in C?

[T.T.T.]

what if the string actually contained the "%" character. What does SQLite do in that case?

[GrandmasterB]

There'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 '\'

[Jerry Coffin]

@Tommy: I'm not sure what overhead would qualify as a lot from your perspective. Yes, it can be used from the C API.

[RBerteig]

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...

[Bren]

warning: this is case insensitive.

[Ahmed Rajab]

does it return true if the string starts or ends with 'omm' @GrandmasterB

[GrandmasterB]

@AhmadRajab in that example, it returns all rows that have "omm" anywhere in the string.

[Ahmed Rajab]

can I use GLOB instead of LIKE to avoid case insensitivity @GrandmasterB

[GrandmasterB]

@AhmadRajab it looks like it, yes. I don't think that was available back when I first wrote this answer, but it looks like it is something that is available now.

[Jason]

I found this slightly ambiguous. By If you need to do such searches do 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.

[Mattwmaster58]

Official docs since edit queue is full: sqlite.org/lang_corefunc.html#instr This answer feels a lot less hacky too!