Search multiple columns in Android SQLite search

Question

I have a cars sqlite database with one table that has 3 columns: year, make and model. With the below query, I can search on a single phrase like "ford" and it will display all ford results, or "1964" and I'll get all cars from that year. But "1964 ford" returns "no results found" - or null. How can the query be modified so that I can enter search terms from all 3 columns like "1964 ford mustang"? Yes, I know there's no such thing as a 1964 ford mustang, it's 1964 and a half. But there is a row for a 1964 ford mustang in my DB, so let's use "1966 ford mustang" as the example.

    public Cursor searchDB(String query) {  
    return db.query(true, DB_TABLE, new String[] { KEY_ROWID, KEY_YEAR, KEY_MAKE, 
    KEY_MODEL }, KEY_YEAR + " LIKE" + "'%" + query + "%' OR " + KEY_MAKE +  
    " LIKE" + "'%" + query + "%' OR " + KEY_MODEL + " LIKE" + "'%" + query + "%'",  
    null, null, null, null, null);  
}

I've tried using AND in place of + but that crashes. I'm using android 2.2.

The correct syntax is AND. + will not work. Logcat output for the crash when using AND please. — Simon
– Simon, Commented Sep 20, 2012 at 17:27
@Simon I disagree. The +'s are Java's String operator for concatenation (as in "Ford " + "Mustang" equals "Ford Mustang"). The syntax of CaptKirk's query is correct, the logic is faulty. — Sam
– Sam, Commented Sep 20, 2012 at 17:49
Ah, I see now. Complete brain freeze. Was reading it as "X LIKE 'foo" AND Y LIKE 'bar'. Forget I even existed :) — Simon
– Simon, Commented Sep 20, 2012 at 18:03

Michael Celey · Accepted Answer · 2012-09-20 18:16:50Z

6

The reason it's not working is your column LIKE %query% statements. That's saying that the value has to contain your query exactly with any number of characters on either end. So "1964" LIKE "%1964 mustang%" would fail but "1964 mustang" LIKE "%1964%" would give you the proper result.

If you split your query string by spaces and query each column against each split string, that would likely give you the results you're looking for.

UPDATE: Snippet for splitting the string and forming the query.

public Cursor searchDB(String query) {
    /* Example: query = "1964 ford mustang" */

    String[] parts = query.split(" "); /* Should split to {"1964", "ford", "mustang"} */

    String queryString = "";
    for(int i = 0; i < parts.length; i++) {
        queryString += KEY_YEAR + " LIKE '%" + parts[i] + "%' OR ";
        queryString += KEY_MAKE + " LIKE '%" + parts[i] + "%' OR ";
        queryString += KEY_MODEL + " LIKE '%" + parts[i] + "%'";
        if(i != (parts.length - 1)) {
            queryString += " OR ";
        }
    }

    return db.query(true, DB_TABLE, 
        new String[] { KEY_ROWID, KEY_YEAR, KEY_MAKE, KEY_MODEL }, 
        queryString, null, null, null, null, null);
}

edited Sep 20, 2012 at 18:16

answered Sep 20, 2012 at 17:35

Michael Celey

12.7k6 gold badges61 silver badges63 bronze badges

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

5 Comments

Sam Over a year ago

(upvote) But I'm not sure that blindly splitting the query by spaces is the best route. Consider a "2000 Subaru Forester 2.5 L", this would return every vehicle made in the year 2000 and every make or model "L" in it including "Lamborghini".

Michael Celey Over a year ago

I agree that it's not the best solution for splitting up the query in all cases but it worked for the cases he provided and would allow him to get relevant results. After he got that working he could move in a direction that would better limit his query such as removing strings shorter than 3 characters or removing strings with a decimal or comma or whatever he found worked best for him. I was going for working and allowing the poster to narrow his results down as he sees fit. Thanks for the support though!

CaptKirk Over a year ago

MCeley, as you can tell by my rep, I'm a rookie at this - both android and sqlite. Can you give me a code snippet example of what you mean by splitting the query? Do you mean rather than KEY_YEAR + " LIKE" + "'%" + query + "%' OR.... I should do KEY_YEAR + " LIKE" + " " + query + " OR...

Michael Celey Over a year ago

Updated answer to provide a snippet of what the split might look like. I don't promise that this will yield the exact results you're looking for but it'll be a good place to start. Also, there's no error checking in this for null queries so be careful what you send it.

CaptKirk Over a year ago

Thank you very much MCeley! It works like a charm. And thank you for writing the query, now I understand what you were saying about splitting the query. And I also see what you mean by it not being the best solution, too. It works almost too well. Instead of not getting any results for a multi-column query, now I get even the irrelevent ones. But hey, that's better than no results.

Collectives™ on Stack Overflow

Search multiple columns in Android SQLite search

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related