CODEWITHSUNDEEP
androidsqlite
Code Poet
Code Poet

Reputation: 8033

SqLite search for five words in one query

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.

Upvotes: 0

Views: 608

Answers (1)

Rahul Pareta
Rahul Pareta

Reputation: 796

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

Upvotes: 1

Related Questions