POSITION, INSTR, CHARINDEX, and $FIND
POSITION, INSTR, CHARINDEX, and $FIND all search a string for a specified substring and return an integer position corresponding to the first match. CHARINDEX, POSITION, and INSTR return the integer position of the first character of the matching substring. $FIND returns the integer position of the first character after the end of the matching substring. CHARINDEX, $FIND, and INSTR support specifying a starting point for substring search. INSTR also supports specifying the substring occurrence from that starting point.
The following example demonstrates these four functions, specifying all optional arguments. Note that the positions of string and substring differ in these functions:
SELECT POSITION('br' IN 'The broken brown briefcase') AS Position,
CHARINDEX('br','The broken brown briefcase',6) AS Charindex,
$FIND('The broken brown briefcase','br',6) AS Find,
INSTR('The broken brown briefcase','br',6,2) AS Inst
For a list of functions that search for a substring, refer to String Manipulation.