2

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 wich 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?

GPo
  • 23
  • 1
  • 3
  • 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

3 Answers3

3

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

This searches for string like T Audios1, Audios123, T Audios123, etc.

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

Sandip Agarwal
  • 1,860
  • 5
  • 28
  • 42
2

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 occurrences. There are more operators in the SQLite documentation as well.

Makoto
  • 100,191
  • 27
  • 181
  • 221
0

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.

Roger Binns
  • 3,033
  • 1
  • 21
  • 33