SQL Server replace function bulk replace the specified string reference method within a specified field in a database

Source: Internet
Author: User
Tags copy expression sql reference string table name access database access
Grammar
REPLACE (' String_expression1 ', ' string_expression2 ', ' String_expression3 ')
Parameter description
' 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.

Popular understanding is the format:
Update table Name set the column =replace to replace (the column to replace, the replaced character, the character to replace)
Sample SQL statement:
Update tablename SET columename = REPLACE (Columename, ' A ', ' B ')

However, it is worth noting that SQL Server has the Replace function, which can be used directly, and that the Replace function of an Access database can only be used in an access environment and not in Jet SQL, so it is useless for ASP, Calling this function in ASP prompts an error: the ' REPLACE ' function in the expression is undefined. In ASP, you can write a function to implement this function.
Sample functions:
Copy CodeThe code is as follows:
function replace (title)
{
Replace (title, ' aaa ', ' bbbb ')
Return (title)
}
Bbb=replace (title)
Update ... set title= ' &bbb& '

Asp+access Bulk Replace the specified character reference code:
Copy CodeThe code is as follows:
<%
Set Conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & Server.MapPath ("database name. mdb")
Set rs = Server.CreateObject ("ADODB.") Recordset ")
Sql= "Select * from [table name]"
Rs.Open sql,conn,1,3
While not rs.eof
RS ("field name") =replace (RS ("field name"), "replaced by character", "character replaced by")
Rs.update
Rs.movenext
Wend
Rs.close
Set rs=nothing
Conn.close
Set conn=nothing
%>


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.