Use the patindex function to search for the starting position of the first occurrence of a specified pattern in a text, unitext, 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. This 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%"
You can use the keyword like to search a unitext column for a specific pattern. However, the like clause is not optimized when it is used with a unitext column. like pattern matching for unitext depends on the default Unicode sort order, which is also used for like pattern matching for unichar and univarchar datatypes.
