Use of the SQL Server replace function; The use of PATINDEX functions in SQL

Use of the SQL Server replace function

REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Grammar
REPLACE (' String_replace1 ', ' string_replace2 ', ' string_replace3 ')

Parameters
"' String_replace1 '

The string expression to search for. String_replace1 can be either character data or binary data.

"' String_replace2 '

The string expression to be found. String_replace2 can be either character data or binary data.

"' String_replace3 '

The replacement string expression. String_replace3 can be either character data or binary data.

return type
If String_replace (1, 2, or 3) is one of the supported character data types, character data is returned. If String_replace (1, 2, or 3) is one of the supported binary data types, the binary data is returned.

Example
The following example replaces the string CDE in Abcdefghi with XXX.

SELECT REPLACE (' abcdefghicde ', ' cde ', ' xxx ') GO
Here is the result set:

------------abxxxfghixxx (1 row (s) affected)

Syntax format: PATINDEX ('%pattern% ', expression)

Returns the position of the pattern string where it first appears in expression expressions, starting at 1.

The pattern string returns 0 if it is not found in expression expressions, and is a valid data type for all valid literals and strings.

Describe the specific use of this function:

1. PATINDEX ('%pattern% ', expression)

The usage of '%pattern% ' is similar to the use of like '%pattern% ', which is to find out whether the pattern string is found by expression, find and return where it first appears.

Such as:

Select Patindex ('? b% ', ' Abcaabbeeabb ')

The result is 5, where ABB first appeared.

2.PATINDEX ('%pattern ', expression)

'%pattern ' is similar to the usage of like '%pattern ', in front of a fuzzy lookup, which is to find the location of the expression where the pattern ends, that is, to find it from the back.

Such as:

Select Patindex ('? b ', ' Abcaabbeeabb ')

Returns 10, which is where ABB first appeared in the rear.

Select Patindex ('? b ', ' abcaabbeeabbr ')

Returns 0, followed by the first letter R and ABB does not match, so returns 0

3.PATINDEX (' pattern% ', expression)

' pattern% ' is similar to like ' pattern% ', which is also used to find the exact front, and then the Fuzzy Lookup, which is equivalent to the first occurrence of the query pattern.

Such as:

Select Patindex (' abb% ', ' Abbcaabbeeabb ')

Returns 1, which is equivalent to the starting value

Select Patindex (' abb% ', ' Arbbcaabbeeabb ')

Returns 0, returns 0 at the beginning and no matter how much is behind.

4.PATINDEX (' pattern ', expression)

This is equivalent to exact matching lookups, which means that pattern,expression is exactly equal.

Such as:

Select Patindex (' ABB ', ' ABB ')

Returns 1, exactly equal

Select Patindex (' ABB ', ' abbr ')

Returns 0, not exactly equal

5. Patindex ('%[pattern]% ', ' ddabcaabbeeabbr '), Patindex ('%[^pattern]% ', ' ddabcaabbeeabbr ')

In this case, [] the usage, [] is to specify certain special characters. [^] a string other than [], [] each of these.

Such as:

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

Such as:

Select Patindex ('%[d]% ', ' RCRDSDDDDAADADFFDR ')

Returns the position of the first occurrence of D in 4,[] in the string rcrdsddddaadadffdr.

Select Patindex ('%[cd]% ', ' RCRDSDDDRDAADADFFDR ')

Returns the position of c,d in one of the 2,[], returns the first occurrence of this position, C in this string where the position is 2, and D is 4, the result takes the first one.

Select Patindex ('%[sd]% ', ' RCRDSDDDRDAADADFFDR ')

Returns the position of c,d in one of the 4,[], returns the first occurrence of this position, s in this string where the position is 5, and D is 4, the result takes the first one.

Select Patindex ('%[^r]% ', ' RRRDSDDDRDAADADFFDR ')

Returns 4, except for the match character of the string in [], the first occurrence of D is not in [^r], so the first time this is found.

Select Patindex ('%[^rd]% ', ' RRRDSDDDRDAADADFFDR ')

Returns 5, except for the match character of the string in [], the first occurrence of S is not in [^rd], so the first time this is found.

Select Patindex ('%[^rsd]% ', ' RRRDSDDDRDAADADFFDR ')

Returns 11, except for the match character of the string in [], the first occurrence of a is not in [^RSD], so the first time this is found.

Use of the SQL Server replace function; The use of PATINDEX functions in SQL