[Solved] How to count the number of instances of a substring in sqlite or duckdb across all rows?
Here’s a great answer from the DuckDB people: Two functions will do the trick for you! String_split, and unnest. Unnest is a special function that takes lists and creates a separate row for each element. With lists_split_into_rows as ( select col1, unnest(string_split(col2, ‘ ‘)) as new_column from my_table ) Select new_column, count(*) as my_count from … Read more