SQLite count number of occurence of word in a string

Question

I want to count number of occurrences of a word in a string for example ,

[{"lastUpdatedDateTime":{"timestamp":1.54867752522E12},"messageStatus":"DELIVERED","phoneNumber":"+916000060000"},{"lastUpdatedDateTime":{"timestamp":1548677525220},"messageStatus":"DELIVERED","phoneNumber":"+916000060000"}]

in above string i want to count no of occurrences of a word 'DELIVERED' here it is 2. i want to get result 2. pls help me on this. i should have to use only sql query to achieve this.

thanks in advance.

score 20 · Accepted Answer · answered Jan 28 '19 at 16:46

20

If your table's name is tablea and the column's name is col:

SELECT 
(LENGTH(col) - LENGTH(REPLACE(col, '"DELIVERED"', ''))) 
/ 
LENGTH('"DELIVERED"') as counter 
from tablea

remove every occurrence of "DELIVERED" and subtract the length of the string from the original string and finally divide the result with the length of "DELIVERED"

answered Jan 28 '19 at 16:46

forpas

160,666
10
38
76

Thanks for your immediate help. It works in sqlite browser. But, replace not supporting within Room database query like its not an valid query – Krishna Jan 29 '19 at 05:56
I have never used Room, but it seems strange that it does not allow a valid sql statement. – forpas Jan 29 '19 at 10:29
Beautiful, i used it for ordering by "counter" – bertasoft May 26 '21 at 08:41

score 2 · Answer 2 · answered Jan 28 '19 at 20:44

Assuming your data is in a table something like:

CREATE TABLE example(json TEXT);
INSERT INTO example VALUES('[{"lastUpdatedDateTime":{"timestamp":1.54867752522E12},"messageStatus":"DELIVERED","phoneNumber":"+916000060000"},{"lastUpdatedDateTime":{"timestamp":1548677525220},"messageStatus":"DELIVERED","phoneNumber":"+916000060000"}]');

and your instance of sqlite has the JSON1 extension enabled:

SELECT count(*) AS "Number Delivered"
FROM example AS e
JOIN json_each(e.json) AS j
WHERE json_extract(j.value, '$.messageStatus') = 'DELIVERED';

gives you:

Number Delivered
----------------
2

This will return the total number of matching entries from all rows in the table as a single value. If you want one result per row instead, it's an easy change but the exact details depend on your table definition. Adding GROUP BY e.rowid to the end of the query will work in most cases, though.

In the long run it's probably a better idea to store each object in the array as a single row in a table, broken up into the appropriate columns.

Thank you for your help. I tried to it's not working got error in json_each and also room sqlite db doesn't support json_each — Krishna, Jan 29 '19 at 05:57

SQLite count number of occurence of word in a string

2 Answers2