Scalar. Determines the position of the nth occurrence of a pattern within a source string.
patindex ( string, pattern, number [, position] [, constant_string] )
| string | A source string. |
| pattern | String representing the pattern to search for. |
| number | Occurence of the pattern to look for. |
| position | (optional) Starting position (0 based index) of the search. Default is 0. |
| constant_string | (optional) Boolean indicating whether the pattern argument should be treated as a constant string instead of a pattern. Default is false. |
Determines the position of the nth occurrence of a pattern within a source string. The pattern can contain wildcards: "_" matches a single arbitrary character; "%" matches 0 or more arbitrary characters. If fewer than n instances of the pattern are found in the string, the function returns -1.
The function takes strings for the string and the pattern arguments, and integers for the number and position arguments. The constant_string argument is a Boolean. The function returns an integer representing the position of the nth occurrence of the pattern within the given string.
If number is less than or equal to zero, the function returns NULL. If position is less than 0, the function starts searching from the start of the string. If position is greater than the length of the string argument, patindex() returns -1.
The function works with UTF-8 strings if the -U server option is specified.
patindex('longlonglongstring', 'long', 2) returns 4.
patindex('longstring', 'long', 2) returns - 1.
patindex('String', __n, 1) returns 2.
patindex('String', %n, 1) returns 0.
patindex('String', __n, 1, false) returns 2.
patindex('String', __n, 1, true) returns -1.
patindex('String', S, 1, 0, false) returns 0.
patindex('Stringi', i, 2, 2, true) returns 6.