The PATINDEX function is used with the wildcard characters. You must enclose the wildcard characters before (when searching at last) or after (when looking for first) the searching text.

But, the CHARINDEX function cannot be used any wildcard characters with the specified searching pattern. In the CHARINDEX function will not work upon the wildcard characters.

SELECT (PATINDEX('%S__p%', '21Snippets'))

Result: 3.

SELECT (CHARINDEX('Snip', '21Snippets'))

Result: 3.