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.

Answer 1

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"

Answer 2

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.