访问 VBA | 如何用另一个字符串替换部分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8550240/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access VBA | How to replace parts of a string with another string
提问by Asynchronous
I am trying to create a piece of code that replaces one word with another. Example: Replace Avenue with Ave and North with N. I am using MS Access, I could use SQL REPLACE Function but I want to do this in VBA using Access module so that I can attached the function to other column.
我正在尝试创建一段代码，用另一个词替换一个词。示例：将 Avenue 替换为 Ave，将 North 替换为 N。我正在使用 MS Access，我可以使用 SQL REPLACE 函数，但我想使用 Access 模块在 VBA 中执行此操作，以便可以将该函数附加到其他列。
I am not sure where to start with this, so any input will be greatly appreciated.
我不确定从哪里开始，所以任何输入都将不胜感激。
Guy
盖伊
回答by Mitch Wheat
Use Access's VBA function Replace(text, find, replacement):
使用 Access 的 VBA 功能Replace(text, find, replacement)：
Dim result As String
result = Replace("Some sentence containing Avenue in it.", "Avenue", "Ave")
回答by Dave 2.71828
I was reading this thread and would like to add information even though it is surely no longer timely for the OP.
我正在阅读此线程并希望添加信息，即使对于 OP 来说肯定不再及时。
BiggerDon above points out the difficulty of rote replacing "North" with "N". A similar problem exists with "Avenue" to "Ave" (e.g. "Avenue of the Americas" becomes "Ave of the Americas": still understandable, but probably not what the OP wants.
BiggerDon 上面指出了用“N”死记硬背替换“North”的难度。“Avenue”到“Ave”存在类似的问题（例如，“Avenue of the Americas”变成“Ave of the Americas”：仍然可以理解，但可能不是 OP 想要的。
The replace() function is entirely context-free, but addresses are not. A complete solution needs to have additional logic to interpret the context correctly, and then apply replace() as needed.
replace() 函数完全与上下文无关，但地址不是。一个完整的解决方案需要有额外的逻辑来正确解释上下文，然后根据需要应用 replace()。
Databases commonly contain addresses, and so I wanted to point out that the generalized version of the OP's problem as applied to addresses within the United States has been addressed (humor!) by the Coding Accuracy Support System (CASS). CASS is a database tool that accepts a U.S. address and completes or corrects it to meet a standard set by the U.S. Postal Service. The Wikipedia entry https://en.wikipedia.org/wiki/Postal_address_verificationhas the basics, and more information is available at the Post Office: https://ribbs.usps.gov/index.cfm?page=address_info_systems
数据库通常包含地址，因此我想指出，编码准确性支持系统 (CASS) 已解决（幽默！）适用于美国境内地址的 OP 问题的广义版本。CASS 是一种数据库工具，它接受美国地址并完成或更正它以满足美国邮政服务设定的标准。维基百科条目https://en.wikipedia.org/wiki/Postal_address_verification具有基础知识，更多信息可在邮局获得：https: //ribbs.usps.gov/index.cfm?page=address_info_systems
回答by Matt Donnan
You could use a function similar to this also, it would allow you to add in different cases where you would like to change values:
您也可以使用与此类似的函数，它允许您在要更改值的不同情况下添加：
Public Function strReplace(varValue As Variant) as Variant
Select Case varValue
Case "Avenue"
strReplace = "Ave"
Case "North"
strReplace = "N"
Case Else
strReplace = varValue
End Select
End Function
Then your SQL would read something like:
然后您的 SQL 将读取如下内容：
SELECT strReplace(Address) As Add FROM Tablename
回答by BiggerDon
Since the string "North" might be the beginning of a street name, e.g. "Northern Boulevard", street directions are always between the street number and the street name, and separated from street number and street name.
由于字符串“North”可能是街道名称的开头，例如“Northern Boulevard”，街道方向总是在街道编号和街道名称之间，并与街道编号和街道名称分开。
Public Function strReplace(varValue As Variant) as Variant
Select Case varValue
Case "Avenue"
strReplace = "Ave"
Case " North "
strReplace = " N "
Case Else
strReplace = varValue
End Select
End Function
