The PATINDEX function returns a 64 bit unsigned integer containing the position of the first occurrence of the specified pattern in a LONG VARCHAR column. PATINDEX returns a 32 bit unsigned integer position for CHAR and VARCHAR columns.
PATINDEX( ‘%pattern%’, long-varchar-column )
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 column value, and zero (0) if the pattern does not occur at the beginning of the column value. Similarly, if the trailing percent wildcard is omitted, the pattern should occur at the end of the column value. The pattern uses the same wildcards as the LIKE comparison.
Patterns without wildcards (percent % or underscore _) can be up to 255 bytes in length.
long-varchar-column The name of the LONG VARCHAR column.
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.
If the LONG VARCHAR cell being searched contains more than one instance of the string pattern, PATINDEX returns only the position of the first instance.
If the column does not contain the pattern, the PATINDEX function returns zero (0).
Searching for a pattern longer than 126 bytes returns NULL.
Searching for a zero-length pattern returns 1.
If any of the arguments is NULL, the result is zero (0).
PATINDEX does not support searching LONG BINARY columns.
For a full description and examples of the PATINDEX function, see “PATINDEX function [String]” in Chapter 4, “SQL Functions”of Reference: Building Blocks, Tables, and Procedures.
For more information on LIKE comparisons, see “LIKE conditions” in Chapter 2, “SQL Language Elements” of Reference: Building Blocks, Tables, and Procedures.
