Comparing CHARINDEX and PATINDEX
The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. PATINDEX can use wildcard characters while 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 (no wildcard characters).
- A string-valued expression, usually a column name, in which Microsoft® SQL Server™ searches for the specified pattern.
For example, find the position at which the pattern "wonderful" begins in a certain row of the notes column in the titles table.
USE pubs
SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'
Here is the result set:
----------------
46
(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.
For example, to use wildcards to find the position at which the pattern "candies" begins in any row of the Description column in the Categories table:
USE Northwind
GO
SELECT CategoryID, PATINDEX('%candies%', Description)AS POSITION
FROM Categories
WHERE PATINDEX('%candies%', Description) <> 0
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.
PATINDEX is useful with text data types; it can be used in a WHERE clause in addition to IS NULL, IS NOT NULL, and LIKE (the only other comparisons that are valid on text in a WHERE clause).