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

pattern – is a character expression of the char or varchar datatype that may include any of the pattern-match wildcard characters supported by the SAP ASE server. The % wildcard character must precede and follow pattern (except when searching for first or last characters)..
char_expr – is a character-type column name, variable, or constant expression of char, varchar, nchar, nvarchar, text_locator, or unitext_locator type.
uchar_expr – is a character-type column name, variable, or constant expression of unichar, or univarchar type.
using – specifies a format for the starting position.
bytes – returns the offset in bytes.
chars or characters – returns the offset in characters (the default).

Examples

Example 1 – 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

Example 3 – 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

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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute patindex.