CHARINDEX() can only be used to search a literal string in the specified expression. In other words you cannot use wildcards. PATINDEX() provides this capability. It takes two arguments, the pattern to be searched and the expression.

– © 2011 – Vishal (http://SqlAndMe.com)
 
DECLARE @string VARCHAR(128)
SET @string =     'PATINDEX searches the string for the ' +
'first occurrence of a specified ' +
'character/String/pattern'
SELECT      PATINDEX('%s_t%', @string) AS 's t',
            PATINDEX('%pat%', @string) AS 'PATINDEX',
            PATINDEX('%pat[^i]%', @string) AS 'pattern',
            PATINDEX('%f[a-i]r%', @string) AS 'first' 

Result Set:

s t   PATINDEX  pattern  first
———–  ———–      ———–     ———–
17    1         87       38
 
(1 row(s) affected)

First column uses the _ Wildcard, which matches any one character, which is between 's' and 't'.

The second column uses % Wildcard, which searches for 0 or more characters. It is same as using: CHARINDEX('pat', @string).

Third column uses a ^ Wildcard, which matches the characters not matching the specified set. Hence, ignoring the 'PATINDEX'.

Last column matches the characters specified in the range using [ ] Wildcard. Since the set specifies that only 'a' to 'i', it ignores the 'for'.

Pattern matching is based on the data collation. We can use COLLATE to enforce a case-sensitive search:

SELECT      PATINDEX('%[S]tring%', @string) AS 'string',
            PATINDEX('%[S]tring%', @string COLLATE Latin1_General_CS_AI)
            AS 'String'

Result Set:

string      String
———–        ———–
23          80
 
(1 row(s) affected)

Hope This Helps! Cheers!