ASA SQL Reference
SQL Functions
Alphabetical list of functions
Returns an integer representing the starting position of the first occurrence of a pattern in a string.
PATINDEX ( '%pattern%', string_expression )
pattern The pattern to be searched for. 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.
The pattern uses the same wildcards as the LIKE comparison. These are as follows:
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 |
string-expression The string to be searched for the pattern.
PATINDEX returns the starting position of the first occurrence of the pattern. If the pattern is not found, it returns zero (0).
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Compatible with Adaptive Server Enterprise, except that the USING clause is not supported.
The following statement returns the value 2.
SELECT PATINDEX( '%hoco%', 'chocolate' )
The following statement returns the value 11.
SELECT PATINDEX ('%4_5_', '0a1A 2a3A 4a5A')