I was wondering if someone could help me figure out how to count all of the unique occurrences of a particular string(s) from a particular column of a SQL table?

Using this:

index    gender
1        0::Male
2        0::Male
3        0::Male||1::Male
4        0::Male||1::Female
5        0::Male||1::Male||2::Female

To get this:

gender    count
Males     7
Females   2

This is what I tried, but it isn't able to account for multiple occurrences in the same cell:

SELECT
    SUM (CASE WHEN genders LIKE '%Male%' THEN '1' END) AS Males,
    SUM (CASE WHEN genders LIKE '