The charindex and patindex functions return the starting position of a pattern you specify. Both take two arguments, but they work slightly differently, since patindex can use wildcard characters, but charindex cannot. charindex can be used only on char, nchar, unichar, univarchar, varchar, nvarchar, binary, and varbinary columns; patindex works on char, nchar, unichar, univarchar, varchar, nvarchar, and text columns.
Both functions take two arguments. The first is the pattern whose position you want. With patindex, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the preceding %) or last (omit the trailing %) 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.
To find the position at which the pattern “wonderful” begins in a certain row of the notes column of the titles table, using both functions, enter:
select charindex("wonderful", notes),
patindex("%wonderful%", notes)
from titles
where title_id = "TC3218"
------------- -------------
46 46
(1 row affected)
If you do not restrict the rows to be searched, the query returns all rows in the table and reports zero values for those rows that do not contain the pattern. In the following example, patindex finds all the rows in sysobjects that start with “sys” and whose fourth character is “a”, “b”, “c”, or “d”:
select name
from sysobjects
where patindex("sys[a-d]%", name) > 0
name ------------------------------ sysalternates sysattributes syscolumns syscomments sysconstraints sysdepends (6 rows affected)
