SQL replace usage and replace Optimization Methods

SQL replace usage and replace Optimization Methods

SQL replace usage and replace Optimization Methods

Select replace ('abcdefghicde', 'cde', 'xxx ')
GO
UPDATE Stu set name = REPLACE (name, 'abc ','')

REPLACE
Replace all the second given string expressions in the first string expression with the third expression.

Syntax
REPLACE ('string _ expression1', 'string _ expression2', 'string _ expression3 ′)

Parameters
'String _ expression1 ′

String expression to be searched. String_expression1 can be character or binary data.

'String _ expression2 ′

String expression to be searched. String_expression2 can be character or binary data.

'String _ expression3 ′

String expression used for replacement. String_expression3 can be character or binary data.

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

Example
In the following example, replace the string cde in abcdefghi with xxx.

Select replace ('abcdefghicde', 'cde', 'xxx ')
GO

The following is the result set:

----
Abxxxfghixxx
(1 row (s) affected)

SQL replace Optimization Method

Syntax
Replace (expression, find, replacewith [, start [, count [, compare])
The Replace function syntax includes the following parts:
Partial description
Expression is required. String expression that contains the substring to be replaced.
Find is required. The substring to be searched.
Replacewith is required. The substring to replace.
Optional. Start position of the substring search in the expression. If this parameter is ignored, start from 1.
Optional. The number of times the substring is replaced. If ignored, the default value is-1, which indicates all possible replacements.
Compare is optional. Numeric value, indicating the comparison method used to determine the substring. For more information about the value, see "set value.
Set Value
The compare parameter settings are as follows:
Constant Value description
VbUseCompareOption-1 uses the set value of the Option Compare statement for comparison.
VbBinaryCompare 0 performs binary comparison.
VbTextCompare 1.
VbDatabaseCompare 2 is only used for Microsoft Access. Perform the comparison based on the information of your database tutorial.
Return Value
The return value of Replace is as follows:
If Replace returns a value
Expression is a zero-length string ("").
Expression is Null.
Find a duplicate with a length of zero expression.
Replacewith is a duplicate with a length of zero expression, in which all the available find strings are deleted.
Start> Len (expression) is a string of zero length.
Count is a duplicate of 0 expression.
Description
The Return Value of the Replace function is a string, but a substring from the position specified by start to the end of the expression string has been replaced. It is not a copy of the original string from start to end.