Returns the starting position of the first occurrence of a specified 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. |
INT
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.
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.