Usage of the patindex Function

/* Usage of the patindex function. Prepared by: Hopewell, Date: 2006/08/26 */
The usage of the patindex function.
Syntax format: patindex ('% pattern %', expression)
Returns the position where the pattern string appears for the first time in the expression. The start value starts from 1.
If no value is found in expression, the pattern string returns 0, which is a valid data type for all valid texts and strings.
Describe the specific usage of this function:

1. patindex ('% pattern %', expression)
The usage of '% pattern %' is similar to the usage of like '% pattern %', that is, fuzzy search for whether its pattern string is found by expression, and return its first position.
For example:
Select patindex ('% ABB %', 'abcaabbeeabb ')
Result 5 is the position where ABB first appeared.

2. patindex ('% pattern', expression)
'% Pattern' is similar to '% pattern'. Fuzzy search is used before, that is, to find the position of the expression where the end of pattern is located.
For example:
Select patindex ('% ABB', 'abcaabbeeabb ')
Return 10, that is, the position where ABB first appeared.
Select patindex ('% ABB', 'abcaabbeeabbr ')
Returns 0. The first letter R and ABB do not match, so 0 is returned.

3. patindex ('pattern' % ', expression)
'Pattern' % 'is similar to like 'pattern' %'. In this case, exact search is used first, and fuzzy search is followed, which is equivalent to the position where pattern appears for the first time.
For example:
Select patindex ('Abb % ', 'abbcaabbeeabb ')
Returns 1, which is equivalent to the starting value.
Select patindex ('Abb % ', 'arbbcaabbeeabb ')
0 is returned. If no value is found at the beginning, 0 is returned. No matter how many values are returned.

4. patindex ('pattern', expression)
This is equivalent to exact matching, that is, pattern, and expression are completely equal.
For example:
Select patindex ('Abb ', 'Abb ')
Returns 1, completely equal
Select patindex ('Abb ', 'abbr ')
Returns 0, not completely equal

5. patindex ('% [pattern] %', 'ddabcaabbeeabbr '), patindex (' % [^ pattern] % ', 'ddabcaabbeeabbr ')
[] Is a special character. [^] All strings.
For example:
Symbol Meaning
Like '5 [%] '5%
Like '[_] n' _ n
Like '[A-CdF] 'a, B, C, D, or F
Like '[-ACDF]'-, A, C, D, or F
Like '[[]' [
Like ']
Like 'abc [_] d % 'abc_d and abc_de
Like 'abc [DEF] 'ABCD, abce, and abcf
For example:
Select patindex ('% [d] %', 'rcrdsddddaadffdr ')
Returns the first occurrence position of D in the 4, [] string rcrdsddddaadffdr.
Select patindex ('% [cd] %', 'rcrdsdddrdaadadffdr ')
Returns the position where C and D in 2, [] are in one of them, and returns the first location where C appears. The first location where C appears in this string is 2, and D is 4, obtain the first result.
Select patindex ('% [SD] %', 'rcrdsdddrdaadadffdr ')
Returns the position where c, d in 4, [] is in one of the positions, returns the first position where s appears in this string for the first time, and returns the position where D is 4, obtain the first result.
Select patindex ('% [^ r] %', 'rrrdsdddrdaadadffdr ')
Returns 4, except for the string matching characters in []. The first occurrence of D is not in [^ r], so we can find the first character.
Select patindex ('% [^ RD] %', 'rrrdsdddrdaadadffdr ')
Returns 5, except the string matching characters in []. The first occurrence of S is not in [^ RD], so we can find the first character.
Select patindex ('% [^ RSD] %', 'rrrdsdddrdaadadffdr ')
Returns 11, except for the string matching characters in []. The first occurrence of A is not in [^ RSD], so we can find the first such character.