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, see “Pattern matching with wildcard characters”.
is a character-type column name, variable, or constant expression of char, varchar, nchar, nvarchar, text_locator, or unitext_locator 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
Finds all the rows in sysobjects that start with “sys” with a fourth character that 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 0 if pattern is not found.
You can use patindex on all character data, including text and image data.
For text, unitext, and image data, if ciphertext is set to 1, then patindex is not supported. An error message appears.
For text, unitext, and image data, if ciphertext is set to 0, then the byte or character index of the pattern within the plaintext is returned.
For unichar, univarchar, and unitext, patindex returns the offset in Unicode characters. The pattern string is implicitly converted to UTF-16 before comparison, and the comparison is based on the default unicode sort order configuration. For example, this is what is returned if a unitext column contains row value U+0041U+0042U+d800U+dc00U+0043:
select patindex("%C%", ut) from unitable
-----------
4
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, patindex returns 0.
If you give a varchar expression as one parameter and a unichar expression as the other, the varchar expression is implicitly converted to unichar (with possible truncation).
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute patindex.
