PATINDEX
Description
PATINDEX
searches for the pattern
-substring and returns its index (starting from 1) within the input if it was found (zero otherwise).
Supported input column types: STRING
Output column type: INT
Syntax
PATINDEX ( table.column, pattern [, occurrence ] )
table.column: The string column on which the
PATINDEX
operator is executed.pattern: The pattern which is searched for in the input string.
occurrence: Number of the occurrence of the pattern, defaults to
1
. If the n-th occurrence of the pattern exists, the start index of the n-th occurrence is returned. Otherwise, if the n-th occurrence of the pattern does not exist, 0 is returned. This happens whenthe pattern is not found in the input string
the occurrence number is larger than the number of times the pattern occurs
the occurrence number is negative
NOTE: In general, this operator conforms to the behavior of MS SQL Server but the parameter order is changed. For consistency with other operators (such as LIKE), the first parameter of PATINDEX
is the input string and the second parameter is the pattern string.
NULL handling
If any of the inputs (table.column
, pattern
or occurrence
) is NULL, the result will be NULL as well.
Examples
[1] Returns the indices where the pattern | ||||||||||||||||||
| ||||||||||||||||||
|
[2] Having a wildcard in the pattern is also supported. | ||||||||
| ||||||||
|
[3] Using the | |||||||||||||||||||||||||
| |||||||||||||||||||||||||
|
[4] To use the wildcard symbol as an actual character, the wildcard symbol must be escaped. | ||||||||
| ||||||||
|
[5] Returns the indices where the pattern | ||||||||||||||||||
| ||||||||||||||||||
|
[6] Returns the indices where the pattern | ||||||||||||||||||
| ||||||||||||||||||
|
[7] Returns the indices where the pattern | ||||||||
| ||||||||
|
[8] Returns 0 if there is no n-th occurrence of the pattern. | ||||||||||||||||||
| ||||||||||||||||||
|