![OCTET_LENGTH function [String]](arrow-left.gif){kind=icon}
![PERCENT_RANK function [Analytical]](arrow-right.gif){kind=icon}
Returns the starting position of the first occurrence of a specified pattern.
PATINDEX ( '%pattern%', string-expression )
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.
The pattern uses the same wildcards as the LIKE comparison. Table 4-25 lists the 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 |
Patterns without wildcards (percent % or underscore _) can be up to 255 bytes in length.
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 string being searched contains more than one instance of the string pattern, PATINDEX returns only the position of the first instance.
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.
Chapter 4, “Function Support” in Large Objects Management in Sybase IQ
