Count right word from a specific variable

Question

How should I be able to count the word "asdf" in the variable aaa with value "asdf_dd_adf34asdf_sdf"?

DECLARE @aaa varchar;

SET @aaa = 'asdf_dd_adf34asdf_sdf';

Answer 1

Count how many characters goes away if you remove the occurances of the word, and divide by the length of the word:

(len(aaa) - len(replace(aaa, 'asdf', ''))) / len('asdf')