patindex

Returns the starting position of the first occurrence of a specified pattern.

Syntax

patindex("%pattern%", char_expr|uchar_expr[, using 
	{bytes | characters | chars}])

Parameters

Examples

Usage

  • 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).

See also Transact-SQL Users Guide.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute patindex.

Related concepts
Pattern Matching with Wildcard Characters
Related reference
charindex
substring