Reputation: 8033
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
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