Text search in SQLite database - Android

Question

I have a database with 6 columns, 2 of them are text format. When the user enters a string I would like to search for the string in the 2 text columns and return the rows. This is the code I have come up with:

mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_DATE}, 
                                        KEY_BODY + " LIKE '"+search_text+"%' OR "
                                      + KEY_TITLE + " LIKE '"+search_text+"%'" , 
                                        null, null, null,KEY_DATE_INT + " DESC");

I am reasonable sure that this translates to

Select KEY_ROWID, KEY_TITLE, KEY_DATE from DATABASE_TABLE where {(KEY_BODY LIKE 'search_text') OR (KEY_BODY LIKE 'search_text')}

The problem I am having is that this only searches the first word of the columns. For example , if the text in the columns is "This is a test" and search string is "test" the query would not return the row. It will return the row if the search text is "this".

What am I doing wrong and is there a better way to implement text search inside a SQLite database in Android.

This is because you have placed '%' after the search_text only which means that find the records only which starts with search_text and don't bother about whats after it. You need to place the '%' before it to have a perfect filter as shown below in the answer by @Rashmi. — Jibran Khan
– Jibran Khan, Commented Mar 18, 2014 at 5:56

Jibran Khan · Accepted Answer · 2014-03-18 06:51:24Z

8

Please try

mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_DATE}, 
                                            KEY_BODY + " LIKE '%"+search_text+"%' OR "
                                          + KEY_TITLE + " LIKE '%"+search_text+"%'" , 
                                            null, null, null,KEY_DATE_INT + " DESC");

The idea is that if you want your sub-string to be searched then you need to wrap up in %substring% then your sub-string will get matched from whole string.

edited Mar 18, 2014 at 6:51

Jibran Khan

3,2544 gold badges40 silver badges52 bronze badges

answered Mar 18, 2014 at 5:53

NullPointerException

4,0085 gold badges36 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DrkStr Over a year ago

That worked perfectly. Thank you. What does "tobe searched" mean ?

NullPointerException Over a year ago

Sorry for my bad English. What I want to say is that the target string(which is "test" inyour case) you want to search among the full string.

Collectives™ on Stack Overflow

Text search in SQLite database - Android

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related