A function in SQL that looks for occurrences of certain characters in a field and returns a certain string

For example as per attachment, i would like a  function that looks at a single occurrence of females first on the management level field and Gender field and gives an additional column a label for example VP Black women, and at the same time look at the occurrence of both male and female and the management level and gender field and give a label/column field of for example VP Black. I would like to use this label or column in SSRS to show a field grouped by females only and a combination of males and females. Your assistance will be highly appreciated.

for example, the query i had in mind would go like this. Excuse i am a complete novice

select [Management level],[gender],[Corporate grade]
case when patindex(%Black%,[Management level]) AND patindex(%Female%,[Gender key]) the 'VP Black women' else 'VP Black'