The CHARINDEX function returns a 64 bit signed integer containing the position of the first occurrence of the specified string in a LONG VARCHAR column. CHARINDEX returns a 32 bit signed integer position for CHAR and VARCHAR columns.
CHARINDEX( string-expression, long-varchar-column )
string-expression The string for which you are searching. This string is limited to 255 bytes.
long-varchar-column The name of the LONG VARCHAR column.
All the positions or offsets, returned or specified, in the CHARINDEX function are always character offsets and may be different from the byte offset for multibyte data.
If the LONG VARCHAR cell being searched contains more than one instance of the string, CHARINDEX returns only the position of the first instance.
If the column does not contain the string, the CHARINDEX function returns zero (0).
Searching for a string longer than 255 bytes returns NULL.
Searching for a zero-length string returns 1.
If any of the arguments is NULL, the result is NULL.
CHARINDEX also supports searching LONG BINARY columns.
For a full description and an example of the CHARINDEX function, see “CHARINDEX function [String]” in Chapter 4, “SQL Functions” of Reference: Building Blocks, Tables, and Procedures.