CODEWITHSUNDEEP
androidsearchsqliteandroid-sqlite
Reza_Rg
Reza_Rg

Reputation: 3374

Search data from sqlite3 database in android

I have a Sqlite3 database in android, with data are sentences like: "good afternoon" or "have a nice day", now I want to have a search box, to search between them, I use something like this :

 Cursor cursor = sqliteDB.rawQuery("SELECT id FROM category WHERE sentences LIKE '"+ s.toString().toLowerCase()+ "%' LIMIT 10", null);

But it only show "good afternoon" as result if user start searching with first "g" or "go" or "goo" or etc, how can I retrieve "good afternoon" as results, if user search like "a" or "af" or "afternoon".

I mean I want to show "good afternoon" result, if user search from middle of a data in sqlite3 db, not only if user searches from beginning.

thanks!

Upvotes: 0

Views: 223

Answers (1)

tiguchi
tiguchi

Reputation: 5410

Just put the percent sign in front of your query string: LIKE '%afternoon%'. However, your approach has two flaws:

  1. It is susceptible to SQL injection attacks because you just insert unfiltered user input into your SQL query string. Use the query parameter syntax instead by re-writing your query as follows: SELECT id FROM category WHERE sentences LIKE ? LIMIT 10. Add the user input string as selection argument to your query method call

  2. It will be dead slow the bigger your database grows because LIKE queries are not optimized for quick string matching and lookups.

In order to solve number 2 you should use SQLite's FTS3 extension which greatly speeds up any text-related searches. Instead of LIKE you would be using the MATCH operator that uses a different query syntax:

SELECT id FROM category WHERE sentences MATCH 'afternoon' LIMIT 10

As you can see the MATCH operator does not need percent signs. It just tries to find any occurrence of a word in the whole text that is being searched (in your case the sentences column). Read through the documentation of FTS3 I've linked to. The MATCH query syntax provides some more pretty handy and powerful options for finding text in your database table which are pretty similar to early search engine query syntax such as:

MATCH 'afternoon OR evening'

The only (minor) downside to the FTS3 extension is that it blows up the database file size by creating additional search index tables and meta-data. But I think it's well worth it for this use case.

Upvotes: 2

Related Questions