PATINDEX function [String]

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

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

pattern The pattern to be searched for. This string is limited to 255 bytes. If the leading percent wild card 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 wild card, then the two leading percent wild cards are treated as one.

**Table 5-16: PATINDEX pattern wild cards**
Wild card	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

string-expression The string to be searched for the pattern.

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

PATINDEX returns the starting position of the first occurrence of the pattern. If the pattern is not found, it returns zero (0).