CHARINDEX()

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

• expression1 = the character expression that contains the sequence to be found. (varchar)
• expression2 = the character expression to be searched. (varchar)
• start_location = Is an expression at which the search starts. (integer/bigint, optional)

CHARINDEX searches expression2 for expression1 and returns its starting position if found. 

If expression1 is not found within expression2, CHARINDEX returns 0.

The search starts at start_location (if specified).

CHARINDEX is used to check if one string exists in another string.

Notes:

1. expression1 is limited to 8000 characters.
2. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.
3. If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.
4. If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher. If the database compatibility level is 65 or lower, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

http://msdn.microsoft.com/en-us/library/ms186323.aspx