Comparing CHARINDEX and PATINDEX
The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. PATINDEX can use wildcard characters, but CHARINDEX cannot.
These functions take two parameters:
The pattern whose position you want. With PATINDEX, the pattern is a literal string that can contain wildcard characters. With CHARINDEX, the pattern is a literal string without wildcard characters.
A string-valued expression, generally a column name, searched for the specified pattern.
For example, find the position at which the pattern arm starts in a certain row of the Title column in the Document table.
USE AdventureWorks2008R2;
GO
SELECT CHARINDEX('arm', Title)
FROM Production.Document
WHERE DocumentID = '1';
GO
Here is the result set.
----------------
7
(1 row(s) affected)
If you do not restrict the rows to be searched, the query returns all rows in the table and it reports nonzero values for those rows in which the pattern was found, and zero for all others.
The following example shows using wildcard characters to find the position at which the pattern reflector starts in any row of the Title column in the Document table.
USE AdventureWorks2008R2;
GO
SELECT DocumentID, PATINDEX('%reflector%', Title)AS POSITION
FROM Production.Document
WHERE PATINDEX('%reflector%', Title) <> 0;
GO
Here is the result set.
DocumentID POSITION
---------- --------
2 7
3 7
(2 row(s) affected)
If you do not restrict the rows to be searched, the query returns all rows in the table and reports nonzero values for those rows in which the pattern was found.
CHARINDEX cannot be used with text, ntext, and image data types.