SqLite search for five words in one query

Question

I need to search for five words at once in one column. All five words have to exist in the column. With my code, only the first word is being searched for. I understand that I need to loop through "listOfWords" and pass the loop to the query. Would anyone have an example of how to do this? My attempt:

        String query = "SELECT 1 FROM sowpods WHERE field1 IN " + listOfWords;
    Cursor cursor = db.rawQuery(query, null);

    if (cursor.moveToFirst()) {
        for (int i = 0; i < listOfWords.length(); i++) {
            do {
                Toast.makeText(this, "Word exists!", Toast.LENGTH_SHORT).show();

            } while (cursor.moveToNext());
        }
    } else {
        Toast.makeText(this, "Word does not exist", Toast.LENGTH_SHORT).show();
        cursor.close();
        db.close();
    }
    cursor.close();
    db.close();
}

UPDATE: At the moment, the only thing that works for me is this:

        String query = "SELECT 1 FROM sowpods WHERE field1 LIKE 'dojfdgkhdg'";
    Cursor cursor = db.rawQuery(query, null);
    if (cursor.moveToFirst()) {
        Toast.makeText(this, "Yay!", Toast.LENGTH_SHORT).show();
    } else {
        Toast.makeText(this, "No, don't exist!", Toast.LENGTH_SHORT).show();
    }

    String query2 = "SELECT 1 FROM sowpods WHERE field1 LIKE 'cat'";
    Cursor cursor2 = db.rawQuery(query2, null);
    if (cursor2.moveToFirst()) {
        Toast.makeText(this, "Yay!", Toast.LENGTH_SHORT).show();
    } else {
        Toast.makeText(this, "No, don't exist!", Toast.LENGTH_SHORT).show();
    }

    String query3 = "SELECT 1 FROM sowpods WHERE field1 LIKE 'dog'";
    Cursor cursor3 = db.rawQuery(query3, null);
    if (cursor3.moveToFirst()) {
        Toast.makeText(this, "Yay!", Toast.LENGTH_SHORT).show();
    } else {
        Toast.makeText(this, "No, don't exist!", Toast.LENGTH_SHORT).show();
    }

However, it doesn't look right because 1. it requires a lot of repetitive code, 2. I am not sure that having multiple cursors is a very efficient use of resources. I would be incredibly grateful for some more pointers. Cheers!

New update

This is where I'm at right now:

Cursor cursor = db.query(true, "sowpods", new String[]{"field1"}, "field1 IN (?)", new String[]{" 'dog', 'cat', 'horse', 'cow'"}, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                cursor.moveToNext();
                Toast.makeText(this, "Word 1 exists", Toast.LENGTH_SHORT).show();
                cursor.moveToNext();
                Toast.makeText(this, "Word 2 exists", Toast.LENGTH_SHORT).show();
                cursor.moveToNext();
                Toast.makeText(this, "Word 3 exists", Toast.LENGTH_SHORT).show();
                cursor.moveToNext();
                Toast.makeText(this, "Word 4 exists", Toast.LENGTH_SHORT).show();
            } while (cursor.moveToNext());
        }
        return cursor;
    }

This is not returning anything, so any help would be welcome.

score 1 · Answer 1 · answered Aug 09 '17 at 21:18

If you want to search for column that will contain any work from your list then use this query

SELECT 1FROM sowpods 
   WHERE field1 LIKE '%word1%'
   OR field1 LIKE '%word2%'
   OR field1 LIKE '%word3%'

If you want to search for column that will contain all the words

SELECT 1FROM sowpods 
   WHERE field1 LIKE '%word1%'
   AND field1 LIKE '%word2%'
   AND field1 LIKE '%word3%'

If you want to search for your column string contain any work as substring and specific too then use this

SELECT 1 FROM sowpods 
  WHERE CHARINDEX('word1', field1 ) > 0
  OR CHARINDEX('word2', field1 ) > 0
  OR CHARINDEX('word3', field1 ) > 0

If you want to search for your column string contain all work as substring and specific too then use this

 SELECT 1 FROM sowpods 
  WHERE CHARINDEX('word1', field1 ) > 0
  AND CHARINDEX('word2', field1 ) > 0
  AND CHARINDEX('word3', field1 ) > 0

Thanks, this looks promising but I need to divide my "listOfWords" into substrings. I'll do that and get back to you. Thanks again. — Code Poet, Aug 09 '17 at 21:24
Hi, I tried option 3 and I get an error: CHARINDEX, no such function. I also tried option 2 but I get Word does not exist even when all five words are in the database. — Code Poet, Aug 10 '17 at 06:31

SqLite search for five words in one query

1 Answers1