Returns the starting position of the first occurrence of a specified pattern.
patindex("%pattern%", char_expr|uchar_expr [, using
{bytes | characters | chars} ] )
is a character expression of the char or varchar datatype that may include any of the pattern-match wildcard characters supported by Adaptive Server. The % wildcard character must precede and follow pattern (except when searching for first or last characters). For a description of the wildcard characters that can be used in pattern, see “Pattern matching with wildcard characters”.
is a character-type column name, variable, or constant expression of char, varchar, nchar or nvarchar type.
is a character-type column name, variable, or constant expression of unichar, or univarchar type.
specifies a format for the starting position.
returns the offset in bytes.
returns the offset in characters (the default).
Selects the author ID and the starting character position of the word “circus” in the copy column:
select au_id, patindex("%circus%", copy)
from blurbs
au_id ----------- ----------- 486-29-1786 0 648-92-1872 0 998-72-3567 38 899-46-2035 31 672-71-3249 0 409-56-7008 0
select au_id, patindex("%circus%", copy,
using chars)
from blurbs
The same as Example 1:
select au_id, patindex("%circus%", copy,
using chars)
from blurbs
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 syscharsets syscolumns syscomments sysconfigures sysconstraints syscurconfigs sysdatabases sysdepends sysdevices
patindex, a string function, returns an integer representing the starting position of the first occurrence of pattern in the specified character expression, or a zero if pattern is not found.
patindex can be used on all character data, including text and image data.
By default, patindex returns the offset in characters; to return the offset in bytes (multibyte character strings), specify using bytes.
Include percent signs before and after pattern. To look for pattern as the first characters in a column, omit the preceding %. To look for pattern as the last characters in a column, omit the trailing %.
If char_expr or uchar_expr is NULL, returns 0.
If a varchar expression is given as one parameter and a unichar expression is given as the other, the varchar expression is implicitly converted to unichar (with possible truncation).
For general information about string functions, see “String functions”.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute patindex.
