SQL replace usage and replace optimization method

Source: Internet
Author: User
Tags first string numeric value

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.

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.