-1

I have an SQLite database containing about 6,000 records. I want to search through the database and count how many strings match the key I'm looking for. I tried the following code, and it returns the number of rows that contains the key:

 String selectQuery = "SELECT count(*) FROM " + TABLE_NAME + " WHERE "
                + TEXT_COLUMN + " LIKE '%" + word + "%'";

c = mDb.rawQuery(selectQuery, null);
if (c != null && c.getCount() > 0) {
      c.moveToFirst();
      count = c.getInt(0);
}

But I want to count the number of strings that matches the key, even if a row contains more than one.

Is there a way to do that?

Musa Y.
  • 1,747
  • 18
  • 26
  • If i understand right i think you are in a loop of Tables and inside of this one you got a loop with columns? – AmirG Jan 29 '16 at 20:57
  • If that so you can count each number of rows returned per column. – AmirG Jan 29 '16 at 21:02
  • @AmirG sorry didn't understand what you said, but I want to search through the db and count all the strings that matches a key.. For example: I have 5 rows which contains the key I'm looking for, but 2 rows have multiple matching keys... So o want to include them in the count as well. – Musa Y. Jan 29 '16 at 21:10
  • Check this [stack thread](http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) it might help – AmirG Jan 29 '16 at 21:21
  • I would love to know who down-voted me and the reason of doing so!! Especially for a question that I asked more than a week ago. – Musa Y. Feb 07 '16 at 14:23

2 Answers2

0

You can count the occurrence through the looping all the rows. Here is the code snippet-

int count=0;
        String selectQuery  = "SELECT  *  FROM " + TABLE_NAME + " WHERE "
                + TEXT_COLUMN + " LIKE '%" + word + "%'";
        c = mDb.rawQuery(selectQuery, null);
        if (c != null ) {
            while (c.moveToNext()){
                for (int i=0;i<c.getColumnCount();i++){

                    if (c.getString(i).contains(word))//Handle the case of letters if needed.
                        count++;
                }
            }
            c.close;
        }
Sanjeet A
  • 5,171
  • 3
  • 23
  • 40
  • This won't work because you're only selecting the count from that table. You want to select the TEXT_COLUMN value so that you can parse it with your code. You would actually get a 0 with the code as-is, since the count will never contain the word. – Stan Shaw Jan 29 '16 at 21:26
  • I modified his post and put it up as an answer - give that a crack. – Stan Shaw Jan 29 '16 at 21:30
  • 1
    Actually, I deleted the answer. His code won't work at all - because it just checks to see if it contains the word - and increments the counter by 1 if it does. so a column that has the word in it many times will still only increment it by 1. You need to parse the column as a string, check if it has the substring of the word. if it does, you increment by 1, and then check again AFTER the ending of the first occurrence's index - and do that until the substring doesn't exist in the column (incrementing by 1 each time). – Stan Shaw Jan 29 '16 at 21:32
  • I don't see issue with code, @M-Y, can you check if your table is having data? – Sanjeet A Jan 29 '16 at 21:34
  • @Sanjeet Ajnabee Yes, the table has the data.. as I mentioned, the code in the question works but it returns the number of rows only and not the number of matching keys. – Musa Y. Jan 29 '16 at 21:36
  • This is snippet bro , It depends upon the use cases. – Sanjeet A Jan 29 '16 at 21:36
  • See the updated answer, remove the count() function from query. – Sanjeet A Jan 29 '16 at 21:41
  • Doesn't work either.. It only returns number of rows if I change ```=``` to ```LIKE``` – Musa Y. Jan 29 '16 at 21:48
  • You need to use the "LIKE" not the "=",Updated the answer – Sanjeet A Jan 29 '16 at 21:54
  • Fair enough, buy it only returns the number of rows and that's not want I'm after.. I want it to return all the strings that matches the key that I'm looking for. For example one row can contain multiple keys, I want to include them in the count. – Musa Y. Jan 29 '16 at 22:02
0

Based on @Stan Shaw 's suggestion:

This is how I solved it: it might not be the best solution, but it works for me and will use it till I come across a better solution.

Firstly, get all the rows where the matched strings are and then place them in an ArrayList.

Secondly, convert the ArrayList to a String.

Thirdly, count the no of occurrences in the String.

Musa Y.
  • 1,747
  • 18
  • 26