How to retrieve the most frequent occurrences of a sub-value from an SQLITE database with comma-separated values in a column?

I have an SQLITE database with a table named “articles.” Each row in the “articles” table represents an article, and there is a column called “keywords” that contains the categories which the article is associated with, separated by commas (up to a maximum of 5 categories per article).

I am trying to find a way to retrieve the top 10 most popular categories based on the number of articles they are associated with. Essentially, I want to count the occurrences of each category across all articles and then display the top 10 categories.

Here is a simplified structure of my “articles” table:

CREATE TABLE IF NOT EXISTS articles 
(articleID TEXT PRIMARY KEY, 
title TEXT, 
author TEXT, 
keywords TEXT, 
content TEXT, 
description TEXT, 
date TEXT, 
reads INTEGER, 
hearts INTEGER, 
comments TEXT)

Is it possible to achieve this using an SQL query or any other method? If so, how can I do this?

I appreciate any guidance or examples to help me solve this problem. Thank you!

I have tried traditional SQL methods, but most are not successful.