CharIndex and PatIndex both can be used to determine the presence of character or string within sql column data. Both returns the starting position of the first occurrence of the character/word within expression.

However, one major difference between CharIndex and PatIndex is that later allows the use of wild card characters while searching for character or word within column data.

Also, Patindex is useful for searching within Text datatype.

Allowed wild card characters are % and _ .

" % "  - use it for any number of characters

" _ "  - use it for a single character.

Syntax

PATINDEX('%pattern%', string_expression)

Note - it's mandatory to include pattern within %% characters.

• returns starting position of occurrence of pattern, if found.
• returns 0, if not found
• returns NULL , if either pattern or string_expression is null.

Example

1. SELECT fldname FROM tblUsers WHERE PatIndex('%v_pin%', fldname) > 0