Count number of times an unknown substring appears in string

Transact-SQL
1

I have the following String which is an example row from a SQL database

‘Anytime Goal Scorer - Geelong Cats @ Hawthorn Hawks / Anytime Goal Scorer - Geelong Cats v Hawthorn Hawks / Geelong Cats vs Hawthorn Hawks’

The end goal is to count the total number of occurrences of a target substring.

The target substring that first needs to be stored somewhere is the 4 trailing characters before and after the target character i.e. before and after every v, vs or @ that appears in the String

I then need to do the following:

  1. Check if the first 2 occurrences of the target substring are the same:

    1a) If the first 2 occurrences aren't the same, we don't need to continue checking and
    return 0

    1b) if they are, continue, and check the remainder of the string for more and count
    the total number of matches

Note: The target substring is unknown for each row. We just know that the target substring will be either side of the v or vs or @

In the case of the above example, the return would be 3, given that the same substring in all cases appears before and after the v, vs and @, Geelong Cats and Hawthorn Hawks

2

Transact SQL is not really suited for string manipulation so I would be inclined to do this in the middle tier. If you really need to do this on the database server you could look at using the CLR. Maybe String_Count in the following: