SQL replace usage and replace Optimization Methods

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.