How do I search for a part of a string in sqlite3?

Question

I want to create search for one of my sqlite3 tables. With a combination of a text-field or pull-down, the search should start after 3 letters have been entered and should return some recommendations which it found in the table. That's the idea at least.

For now I'm stuck at searching for a part of a string in the database.

"SELECT * FROM album WHERE artist='Audiosl'"

That does return a empty search as well as

"SELECT * FROM album WHERE artist LIKE 'Audiosl'"

Is there a way to do that?

if there is enough memory then [`trie` or `suffixtree` might be faster](http://stackoverflow.com/a/5479374/4279) for an implementation of autocomplete. — jfs, Jan 23 '12 at 05:18

score 3 · Accepted Answer · edited Jun 18 '23 at 13:53

3

"SELECT * FROM album WHERE artist LIKE '%Audiosl%'" would do your task.

This searches for string like T Audiosl, Audiosl23, T Audiosl23, etc.

Refer for more : http://www.sqlite.org/lang_expr.html

edited Jun 18 '23 at 13:53

cafce25

15,907
4
25
31

answered Jan 23 '12 at 04:41

Sandip Agarwal

1,890
5
28
42

score 3 · Answer 2 · edited Jun 18 '23 at 13:53

3

Take a closer look into using the LIKE SQLite operator. This would turn your statement into the following:

SELECT * FROM album WHERE artist LIKE 'Aud%'"

The % matches on zero or more characters. There are more operators in the SQLite documentation as well.

edited Jun 18 '23 at 13:53

cafce25

15,907
4
25
31

answered Jan 23 '12 at 04:41

Makoto

104,088
27
192
230

score 0 · Answer 3 · answered Jan 23 '12 at 11:00

SQLite includes an official full text search extension which provides functionality suitable for lots of text fields as your schema implies. You can do field style searches (artist:*a* album:"greatest hits") plus all the other stuff you would expect (NEAR, AND, OR, exclusions).

I strongly recommend you use this extension instead of building your own queries.

How do I search for a part of a string in sqlite3?

3 Answers3