sql substring contains

I have a datasetA with a long narrative field. This field is called "narrative."

I have datasetB full of animal terms, such as "dog", "cat", "mouse". This field is called "animals."

I would like to flag any instance where the animal names are found in the narrative of datasetA, and to create a new field in datasetA, called "animal_found" which pulls that name.

For example, if the word "dog" is found in a narrative, the animal_found field for that record will populate "dog"

if the word "dog" and "cat" is found, the animal_found field will show "dog,cat"

Any thought on how to code this in SQL?