patindex

Description

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

char_expr

is a character-type column name, variable, or constant expression of char, varchar, nchar or nvarchar 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 2

select au_id, patindex("%circus%", copy, 
     using chars)
from blurbs

Example 3

The same as Example 1:

select au_id, patindex("%circus%", copy, 
     using chars)
from blurbs

Example 4

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

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute patindex.

See also

Functions charindex, substring