Use the patindex function to search for the starting position of the first occurrence of a specified pattern in a text, varchar, or char column. The % wildcard character must precede and follow the pattern (except when you are searching for the first or last character).
You can use the like keyword to search for a particular pattern. The following example selects each text data value from the blurb column of the texttest table that contains the pattern “Straight Talk%”:
select blurb from texttest where blurb like "Straight Talk%"