SQL replace usage and replace optimization method

SELECT REPLACE (' abcdefghicde ', ' cde ', ' xxx ')
Go
UPDATE Stu Set name = REPLACE (name, ' abc ', ')

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

Grammar
REPLACE (' string_expression1′, ' string_expression2′, ' string_expression3′)

Parameters
' string_expression1′

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

' string_expression2′

The string expression to find. String_expression2 can be either character data or binary data.

' string_expression3′

The string expression to replace. String_expression3 can be either character data or binary data.

return type
If String_Expression (1, 2, or 3) is one of the supported character data types, the character data is returned. If String_Expression (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)

SQL Replace optimization method

Grammar
Replace (expression, find, replacewith[, start[, count[, compare]])
The Replace function syntax has the following parts:
Partial description
Expression is required. A string expression containing the substring to replace.
Find required. The substring to search for.
ReplaceWith is required. The substring to replace.
Start is optional. The starting position of the substring search in the expression. If omitted, assume starting from 1.
Count is optional. The number of times the substring was replaced. If omitted, the default value is-1, which indicates that all possible substitutions are made.
Compare is optional. Numeric value that represents the comparison method to use when distinguishing a substring. See the "Set Values" section for their values.
Setting values
The setting values for the Compare parameter are as follows:
Constant numerical description
VbUseCompareOption-1 uses the setting value of the Option Compare statement to perform the comparison.
Vbbinarycompare 0 performs binary comparisons.
vbTextCompare 1 performs a text comparison.
Vbdatabasecompare 2 is for Microsoft Access only. Perform comparisons based on information from your database tutorial.
return value
The return value for replace is as follows:
If replace returns a value
Expression length is a 00-length string ("").
Expression is a null error.
Find a copy with a zero expression length.
The replacewith length is a copy of zero expression, which deletes all occurrences of the find string.
Start > Len (expression) is a zero-length string.
A copy of the count is 0 expression.
Description
The return value of the Replace function is a string, but, starting at the position specified by start, the replacement action has occurred at the end of the expression string. is not a copy of the original string from beginning to end.