[Solved] How to count the number of instances of a substring in sqlite or duckdb across all rows?

by


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 lists_split_into_rows
Group by
  New_column

Source: https://github.com/duckdb/duckdb/issues/4169

0

solved How to count the number of instances of a substring in sqlite or duckdb across all rows?