Pattern Matching in text Data

Use the patindex function to search for the starting position of the first occurrence of a specified pattern in a text, unitext, varchar, univarchar, unichar, 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 also use the like keyword to search for a particular pattern. The following example selects each text data value from the copy column of the blurbs table that contains the pattern “Net Etiquette.”
select copy from blurbs 
where copy like "%Net Etiquette%"