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:
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
|
|
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 “breads” begins in any row of the Description column in the Categories table:
USE Northwind
GO
SELECT CategoryID, PATINDEX('%candies%', LOWER(Description) )
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).