CHARINDEX

Use CHARINDEX to search a character string expression for a specified string. The result is the position number of the string in the original expression.

CHARINDEX(string_to_find, string_to_search, starting_position)

SELECT CHARINDEX('team','baseball team')