The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify.
The CHARINDEX function has the following syntax:
CHARINDEX('pattern', expression)
The PATINDEX function has the following syntax:
PATINDEX('pattern', expression)
Both take two parameters, but work slightly differently because PATINDEX can use wildcard characters (CHARINDEX cannot).
The first parameter is 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). The second parameter is a string-valued expression, usually a column name, in which SQL Server searches for the specified pattern.
For example, to find the position at which the pattern "wonderful" begins in a certain row of the notes column in the titles table:
SELECT CHARINDEX('wonderful', notes)
|
|
FROM titles |
|
WHERE title_id = 'TC3218' |
|
---------------- |
|
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 non-zero values for those rows in which the pattern was found and zero for all others.
To use wildcards to find the position at which the pattern "wonderful" begins in a certain row of the notes column in the titles table:
SELECT PATINDEX('%wonderful%', notes)
|
|
FROM titles |
|
WHERE title_id = 'TC3218' |
|
------------ |
|
46 |
|
(1 row(s) affected) |
|
If you do not restrict the rows to be searched, the query returns all rows in the table and reports non-zero values for those rows in which the pattern was found.
PATINDEX is useful with text datatypes; 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).