Diff between patindex and charindex

http://sqlandme.com/2011/07/05/sql-functions-patindex/

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!

http://blog.sqlauthority.com/2007/04/08/sql-server-search-text-field-charindex-vs-patindex/

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

April 8, 2007 by pinaldave

We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify.

Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.

Example of CHARINDEX:
USE AdventureWorks;
GO
SELECT CHARINDEX('ensure', DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

Examples of PATINDEX:
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

Summary:
PATINDEX is CHARINDEX + WildCard Search. Use either of them depending your need.