PATINDEX Function [String]

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

Syntax

PATINDEX ( '%pattern%', string-expression )

Parameters

Parameter	Description
pattern	The pattern for which you are searching. This string is limited to 126 bytes for patterns with wildcards. If the leading percent wildcard is omitted, PATINDEX returns one (1) if the pattern occurs at the beginning of the string, and zero if not. If `pattern` starts with a percent wildcard, then the two leading percent wildcards are treated as one. Patterns without wildcards (percent % or underscore _) can be up to 255 bytes in length.
string-expression	The string to be searched for the pattern.

Parameter

Description

pattern

The pattern for which you are searching. This string is limited to 126 bytes for patterns with wildcards. If the leading percent wildcard is omitted, PATINDEX returns one (1) if the pattern occurs at the beginning of the string, and zero if not. If pattern starts with a percent wildcard, then the two leading percent wildcards are treated as one.

Patterns without wildcards (percent % or underscore _) can be up to 255 bytes in length.

string-expression

The string to be searched for the pattern.

Returns

INT

Examples

The following statement returns the value 2:

SELECT PATINDEX( '%hoco%', 'chocolate' ) FROM iq_dummy

The following statement returns the value 11:

SELECT PATINDEX ('%4_5_', '0a1A 2a3A 4a5A') FROM iq_dummy

Usage

PATINDEX returns the starting position of the first occurrence of the pattern. If the string being searched contains more than one instance of the string pattern, PATINDEX returns only the position of the first instance.

The pattern uses the same wildcards as the LIKE comparison. This table lists the pattern wildcards.

PATINDEX pattern wildcards
Wildcard	Matches
_ (underscore)	Any one character
% (percent)	Any string of zero or more characters
[]	Any single character in the specified range or set
[^]	Any single character not in the specified range or set

If the pattern is not found, PATINDEX returns zero (0).

Searching for a pattern longer than 126 bytes returns NULL.

Searching for a zero-length string returns 1.

If any of the arguments is NULL, the result is zero (0).

All the positions or offsets, returned or specified, in the PATINDEX function are always character offsets and may be different from the byte offset for multibyte data.

PATINDEX returns a 32 bit unsigned integer position for CHAR and VARCHAR columns.

If you are licensed to use the Unstructured Data Analytics functionality, you can use this function with large object data.

See PATINDEX Function in Unstructured Data Analytics.

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Compatible with Adaptive Server Enterprise.