How to use patindex function in SQL
This article to share with you is about how to use the patindex function in SQL, Xiaobian feel quite practical, so share to everyone to learn, I hope you can gain something after reading this article, not much to say, follow Xiaobian to see it.
Returns the position of the first occurrence of a pattern string in an expression, starting at 1.
The pattern string returns 0 if not found in the expression expression, which is a valid data type for all valid text and strings.
Describe the specific use of this function:
1.PATINDEX('%pattern%',expression)
The usage of '%pattern%' is similar to the usage of like'%pattern %', i.e. it obscures whether its pattern string is an expression found, finds and returns the position of its first occurrence.
For example:
selectpatindex('%abb%','abcaabbeeabb')
Result 5, where abb first appears.
2.PATINDEX('%pattern',expression)
'%pattern' is similar to the usage of like'% pattern', with fuzzy search in front, that is, to find the position of the expression where the pattern ends, that is, to find from the back match.
For example:
selectpatindex('%abb','abcaabbeeabb')
Return to 10, where abb first appears later.
selectpatindex('%abb','abcaabbeeabbr')
Returns 0, the first letter after r and abb do not match, so returns 0
3.PATINDEX('pattern%',expression)
'pattern%' is similar to like'pattern%' in that it is preceded by an exact search and followed by a fuzzy search, which is equivalent to querying the position where the pattern first appears.
For example:
selectpatindex('abb%','abbcaabbeeabb')
Return 1, which is equivalent to the starting value
selectpatindex('abb%','arbbcaabbeeabb')
Returns 0, if it doesn't start, returns 0, whatever comes after it.
4.PATINDEX('pattern',expression)
This is equivalent to an exact match lookup, i.e. pattern,expression are exactly equal.
For example:
selectpatindex('abb','abb')
Return 1, exactly equal
selectpatindex('abb','abbr')
Returns 0, not exactly equal
5.patindex('%[pattern]%','ddabcaabbeeabbr')，
patindex('%[^pattern]%','ddabcaabbeeabbr')
[1][2][3][4][5][6][7][8][9][9][10][11][12][13][14][15][16][17][18][19][19][19][10][19][10][10][10][11][10][10][11][10][11][12][10][11][12][13][14][15] [^] Strings other than [],[] each of which.
For example:
SymbolMeaning
LIKE'5[%]'5%
LIKE'[_]n'_n
LIKE'[a-cdf]'a,b,c,d,orf
LIKE'[-acdf]'-,a,c,d,orf
LIKE'[[]'[
LIKE']']
LIKE'abc[_]d%'abc_dandabc_de
LIKE'abc[def]'abcd,abce,andabcf
For example:
selectpatindex('%[d]%','rcrdsddddaadadffdr')
Returns the position of the first occurrence of d in [] in the string rcrdsdddaadffdr.
selectpatindex('%[cd]%','rcrdsdddrdaadadffdr')
Return 2, c,d in [] at one of the positions, return the first occurrence of this position, c in this string first occurrence position is 2, and d is 4, the result takes the first one.
selectpatindex('%[sd]%','rcrdsdddrdaadadffdr')
Return 4, c,d in [] at one of the positions, return the first occurrence of this position, s in this string first occurrence position is 5, and d is 4, the result takes the first one.
selectpatindex('%[^r]%','rrrdsdddrdaadadffdr')
Returns 4, except for the matching character of the string in [], the first occurrence of d is not in [^r], so the first occurrence is found.
selectpatindex('%[^rd]%','rrrdsdddrdaadadffdr')
Returns 5, except for the matching character of the string in [], the first occurrence of s is not in [^rd], so the first occurrence is found.
selectpatindex('%[^rsd]%','rrrdsdddrdaadadffdr')
Returns 11, except for the matching character of the string in [], the first occurrence of a is not in [^rsd], so the first occurrence is found.
The above is how to use the patindex function in SQL. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
