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.

Answer 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

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

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