I was looking at some code generated by the compiler for an IQueryable that I created and found this built-in function that I’ve never used before.  I can see it being very useful when doing string compares.

CHARINDEX (Transact-SQL)
expressionToFind
Is a character expression that contains the sequence to be found. expressionToFind is limited to 8000 characters.

expressionToSearch
Is a character expression to be searched.

start_location
Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.
…
If either expressionToFind or expressionToSearch is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.
…

via CHARINDEX Transact-SQL.