Pattern Matching with text and unitext Data

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).

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.