如何在 Access SQL 中替换多个字符？

How to Replace Multiple Characters in Access SQL?

提问by Andomar

I'm a novice at SQL, so hopefully someone can spell this out for me. I tried following the "Replace Multiple Strings in SQL Query" posting, but I got stuck.

我是 SQL 的新手，所以希望有人能帮我解释清楚。我尝试按照“替换 SQL 查询中的多个字符串”的帖子进行操作，但我被卡住了。

I'm trying to do the same thing as the originator of the above posting but with a different table and different fields. Let's say that the following field "ShiptoPlant" in table "BTST" has three records (my table actually has thousands of records)...

我正在尝试与上述帖子的发起者做同样的事情，但使用不同的表和不同的字段。假设以下字段“ShiptoPlant表”中的BTST“”有三条记录（我的表实际上有数千条记录）...

Table Name: BTST

表名：BTST

   ---------------
   | ShiptoPlant |
   | ----------- |
   | Plant #1    |
   | Plant - 2   |
   | Plant/3     |
   ---------------

Here's what I'm trying to type in the SQL screen:

这是我尝试在 SQL 屏幕中输入的内容：

SELECT CASE WHEN ShipToPlant IN ("#", "-", "/") Then ""
ELSE ShipToPlant END FROM BTST;

I keep getting the message (Error 3075)...

我不断收到消息（错误 3075）...

"Syntax error (missing operator) in query expression 
'CASE WHEN ShiptoPlant IN (";","/"," ") Then "" ELSE ShipToPlant END'."

I want to do this operation for every character on the keyboard, with exception of "*"since it is a wildcard.

我想对键盘上的每个字符执行此操作，"*"因为它是通配符。

Any help you could provide would be greatly appreciated!

您能提供的任何帮助将不胜感激！

EDIT: Background Information added from the comments

编辑：从评论中添加的背景信息

I have collected line-item invoice-level data from each our 14 suppliers for the 2008 calendar year. I am trying to normalize the plant names that are given to us by our suppliers.

我已经从 2008 日历年的 14 家供应商中收集了行项目发票级别的数据。我正在尝试规范供应商提供给我们的植物名称。

Each supplier can call a plant by a different name e.g.

每个供应商可以用不同的名称来称呼工厂，例如

Signode Serviceon our master list could be called by suppliers

供应商可以调用我们主列表上的Signode 服务

Signode Service 
Signode - Service.
SignodeSvc
SignodeService

I'm trying to strip non-alphanumeric chars so that I can try to identify the plant using our master listing by creating a series of links that look at the first 10 char, if no match, 8 char, 6, 4...

我正在尝试去除非字母数字字符，以便我可以尝试使用我们的主列表来识别植物，方法是创建一系列查看前 10 个字符的链接，如果不匹配，则为 8 个字符、6、4...

My basic hang-up is that I don't know how to strip the alphanumeric characters from the table. I'll be doing this operation on several columns, but I planned on creating separate queries to edit the other columns.

我的基本问题是我不知道如何从表格中去除字母数字字符。我将在几个列上执行此操作，但我计划创建单独的查询来编辑其他列。

Perhaps I need to do a mass update query that strips all the alphanumerics. I'm still unclear on how to write it. Here's what I started out with to take out all the spaces. It worked great, but failed when I tried to nest the replace

也许我需要做一个去除所有字母数字的批量更新查询。我现在还不清楚怎么写。这是我开始取出所有空间的方法。效果很好，但是当我尝试嵌套替换时失败了

UPDATE BTST SET ShipToPlant = replace(ShipToPlant," ","");

EDIT 2: Further Information taken from Comments

编辑 2：从评论中获取的更多信息

Every month, up to 100 new permutations of our plant names appear in our line item invoice data- this could represent thousands of invoice records. I'm trying to construct a quick and dirty way to assign a master_id of the definitive name to each plant name permutation. The best way I can see to do so is to look at the plant, address, city and state fields, but the problem with this is that these fields have various permutations as well, for example,

每个月，我们的订单项发票数据中会出现多达 100 个新的工厂名称排列 - 这可能代表数千个发票记录。我正在尝试构建一种快速而肮脏的方法，将最终名称的 master_id 分配给每个植物名称排列。我能看到的最好方法是查看工厂、地址、城市和州字段，但问题在于这些字段也有各种排列，例如，

128 Brookview Drive
128 Brookview Lane

By taking out alphanumerics and doing

通过取出字母数字并做

LEFT(PlantName,#chars) & _
LEFT(Address,#chars) & _
LEFT(City,#chars) & _
LEFT(State,#chars) 

and by changing the number of characters until a match is found between the invoice data and the Master Plant Listing (both tables contain the Plant, Address, City and State fields), you can eventually find a match. Of course, when you start dwindling down the number of characters you are LEFTing, the accuracy becomes compromised. I've done this in excel and had decent yield. Can anyone recommend a better solution?

通过更改字符数直到在发票数据和主工厂列表（两个表都包含工厂、地址、城市和州字段）之间找到匹配项，您最终可以找到匹配项。当然，当您开始减少输入的字符数时LEFT，准确性会受到影响。我已经在 excel 中完成了这项工作，并且获得了不错的收益。谁能推荐一个更好的解决方案？

回答by Fionnuala

You may wish to consider a User Defined Function (UDF)

您可能希望考虑用户定义函数 (UDF)

SELECT ShiptoPlant, CleanString([ShiptoPlant]) AS Clean
FROM Table


Function CleanString(strText)
Dim objRegEx As Object

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True

objRegEx.Pattern = "[^a-z0-9]"
CleanString = objRegEx.Replace(strText, "")

End Function

回答by Russ Cam

You could use the built in Replacefunction within Access

您可以使用ReplaceAccess 中的内置函数

SELECT
    Replace(Replace(Replace(ShipToPlant, "#", ""), "-", ""), "/", "") AS ShipToPlant
FROM
    BTST

As others have said, within Access you can write your own functions in VBA and use them in your queries.

正如其他人所说，在 Access 中，您可以在 VBA 中编写自己的函数并在查询中使用它们。

EDIT:

编辑：

Here's a way to handle the nested Replace limit by wrappering the Replace function within our own function. It feels dirtybut it works- put this in a module within Access

这是一种通过将 Replace 函数包装在我们自己的函数中来处理嵌套 Replace 限制的方法。感觉很脏但它有效 - 将其放在 Access 中的一个模块中

Public Function SuperReplace(ByRef field As String, ByVal ReplaceString As String) As String
    ' Size this as big as you need... it is zero-based by default' 
    Dim ReplaceArray(3) As String

    'Fill each element with the character you need to replace'  
    ReplaceArray(0) = "#"
    ReplaceArray(1) = "-"
    ReplaceArray(2) = "/"
    ReplaceArray(3) = " "

    Dim i As Integer
    For i = LBound(ReplaceArray) To UBound(ReplaceArray)    
       field = Replace(field, ReplaceArray(i), ReplaceString)
    Next i

    SuperReplace = field    
End Function

Then test it with this query

然后用这个查询测试它

SELECT 
    SuperReplace(ShipToPlant,"") AS ShipToPlant
FROM
    BTST

You might want to take this an expand it so that you can pass in an array of strings instead of hard-coding them into the function.

您可能希望将此扩展为它，以便您可以传入一个字符串数组，而不是将它们硬编码到函数中。

EDIT 2:

编辑2：

In response to the additional information in the comments on the question, here's a suggestion for how you might want to handle the situation differently. The advantage to this apprach is that once you have mapped in a plant name permutation, you won't need to perform a string replace on future data in future years, only add new plant names and permutations to the map.

针对问题评论中的其他信息，这里有一个建议，说明您可能希望如何以不同方式处理这种情况。这种方法的优点是，一旦您在植物名称排列中进行了映射，您就不需要在未来几年对未来数据执行字符串替换，只需将新的植物名称和排列添加到地图中。

Start with creating another table, let's call it plant_map

从创建另一个表开始，我们称之为 plant_map

CREATE TABLE plant_map (id AUTOINCREMENT PRIMARY KEY, name TEXT, master_id LONG)

into plant_map, add all of the permutations for plant names and insert the id for the name you wish to use to refer to a particular plant name permutation group with, into the master_id field. From your comments, I'll use Signode Service

into plant_map，添加植物名称的所有排列，并将您希望用来引用特定植物名称排列组的名称的 id 插入到 master_id 字段中。根据您的评论，我将使用Signode 服务

INSERT INTO plant_map(name, master_id) VALUES ("Signode Service", 1);
INSERT INTO plant_map(name, master_id) VALUES ("Signode Svc", 1);
INSERT INTO plant_map(name, master_id) VALUES ("Signode - Service", 1);
INSERT INTO plant_map(name, master_id) VALUES ("Signode svc", 1);
INSERT INTO plant_map(name, master_id) VALUES ("SignodeService", 1);

Now when you query BTST table, you can get data for Signode Serviceusing

现在，当您查询 BTST 表时，您可以使用以下命令获取Signode Service 的数据

SELECT
    field1,
    field2
FROM
    BTST source
INNER JOIN
    (
    plant_map map1      
    INNER JOIN
    plant_map map2
    ON map1.master_id = map2.id
    )
    ON source.ShipToPlant = map1.name
WHERE
    map2.name = "Signode Service"

Data within table BTSTcan remain unchanged.

表中的数据BTST可以保持不变。

Essentially, this is joining on the plant name in BTSTto the name in plant_mapthen, using master_id, self joining on idwithin plant_mapso that you need only pass in one "common" name. I would advise putting an index on each of the columns nameand master_idin plant_mapas both fields will be used in joins.

从本质上讲，这是对植物的名称加入BTST到名字plant_map，然后，使用master_id，自加入对id内plant_map，这样你只需要传递一个“共同”的名字。我会建议把指数的每一列name和master_id在plant_map为这两个领域将用于连接。

回答by Andomar

Don't think Access supports the CASE statement. Consider using iif:

不要认为 Access 支持 CASE 语句。考虑使用 iif：

iif ( condition, value_if_true, value_if_false )

For this case you can use the REPLACE function:

对于这种情况，您可以使用 REPLACE 函数：

SELECT 
    REPLACE(REPLACE(REPLACE(yourfield, '#', ''), '-', ''), '/', '') 
    as FieldName
FROM
    ....

回答by Praesagus

OK, your question has changed, so the solution will too. Here are two ways to do it. The quick and dirty way will only partially solve your issue because it won't be able to account for the more odd permutations like missing spaces or misspelled words. The quick and dirty way:

好的，您的问题已更改，因此解决方案也将更改。这里有两种方法可以做到。快速而肮脏的方法只能部分解决您的问题，因为它无法解决更奇怪的排列，例如缺少空格或拼写错误的单词。快速而肮脏的方式：

1. Create a new table - let's call it tChar.
2. Put a text field in it - the char(s) you want to replace - we'll call it charfor this example
3. Put all the char or char combinatios that you want removed in this table.

4. Create and run the query below. Note that it will only remove one item at a time, but you can also put different versions of the same replacement in it too like ' -' or '-' For this example I created a table called tPlantwith a field called ShipToPlant.

   SELECT tPlant.ShipToPlant, Replace([ShipToPlant], (SELECT top 1 char FROM tChar WHERE instr(ShipToPlant,char)<>0 ORDER BY len(char) Desc),"" ) AS New FROM tPlant;

1. 创建一个新表 - 我们称之为 tChar。
2. 在其中放置一个文本字段 - 您要替换的字符 - 我们将char在此示例中调用它
3. 将您要删除的所有字符或字符组合放在此表中。

4. 创建并运行下面的查询。请注意，它一次只会删除一个项目，但您也可以将相同替换的不同版本放入其中，例如“-”或“-”在此示例中，我创建了tPlant一个名为ShipToPlant.

   SELECT tPlant.ShipToPlant, Replace([ShipToPlant], (SELECT top 1 char FROM tChar WHERE instr(ShipToPlant,char)<>0 ORDER BY len(char) Desc),"" ) AS New FROM tPlant;

The better (but much more complex) way. This explanation is going to be general because it would be next to impossible to put the whole thing in here. If you want to contact me directly use my user name at gmail.:

更好（但更复杂）的方式。这种解释将是通用的，因为几乎不可能将整个内容放在此处。如果您想直接与我联系，请使用我在 gmail 上的用户名。：

1. Create a table of Qualifiers - mistakes that people enter like svc instead of service. Here you would enter every wierd permutation you get.
2. Create a table with QualifierID and Plant ID. Here you would say which qualifier goes to which plant.
3. Create a query that joins the two and your table with mistaken plant names in it. Use instr so say what is in the fields.
4. Create a second query that aggragates the first. Count the instr field and use it as a score. The entry with the highest score is the plant.
5. You will have to hand enter the ones it can't find, but pretty soon that will be next to none as you have more and more entries in the table.

1. 创建一个限定符表 - 人们输入的错误，如 svc 而不是服务。在这里你会输入你得到的每一个奇怪的排列。
2. 使用 QualifierID 和 Plant ID 创建一个表。在这里你会说哪个限定符去哪个工厂。
3. 创建一个查询，将两者和您的表连接起来，其中包含错误的植物名称。使用 instr 说出字段中的内容。
4. 创建第二个查询来聚合第一个查询。计算 instr 字段并将其用作分数。得分最高的条目是植物。
5. 您将不得不手动输入它找不到的那些，但很快，因为表中的条目越来越多，所以几乎没有。

ughh

呃

---

---

You have a couple different choices. In Access there is no CASE in sql, you need to use IIF. It's not quite as elegant as the solutions in the more robust db engines and needs to be nested for this instance, but it will get the job done for you.

你有几个不同的选择。Access中sql中没有CASE，需要使用IIF。它不像更强大的数据库引擎中的解决方案那么优雅，并且需要为此实例嵌套，但它会为您完成工作。

SELECT
    iif(instr(ShipToPlant,"#")<>0,"",
    iif(instr(ShipToPlant,"-")<>0,"",
    iif(instr(ShipToPlant,"/")<>0,"",ShipToPlant ))) AS FieldName
FROM BTST;

You could also do it using the sql to limit your data.

您也可以使用 sql 来限制您的数据。

SELECT YourID, nz(aBTST.ShipToPlant,"") AS ShipToPlant  
FROM BTST LEFT JOIN (
    SELECT YourID, ShipToPlant 
    FROM BTST 
    WHERE ShipToPlant NOT IN("#", "-", "/")
    ) as aBTST ON BTST.YourID=aBTST.YourID

If you know VB you can also create your own functions and put them in the queries...but that is another post. :) HTH

如果您了解 VB，您还可以创建自己的函数并将它们放入查询中……但那是另一篇文章。:) HTH

回答by DJ.

Create a public function in a Code module.

在代码模块中创建一个公共函数。

Public Function StripChars(ByVal pStringtoStrip As Variant, ByVal pCharsToKeep As String) As String

Dim sChar As String
Dim sTemp As String
Dim iCtr As Integer

  sTemp = ""

  For iCtr = 1 To Len(pStringtoStrip)
    sChar = Mid(pStringtoStrip, iCtr, 1)
    If InStr(pCharsToKeep, sChar) > 0 Then
      sTemp = sTemp & sChar
    End If
  Next

  StripChars = sTemp

End Function

Then in your query

然后在您的查询中

SELECT
    StripChars(ShipToPlant, "abcdefghijklmnopqrstuvwxyz0123456789") AS ShipToPlantDisplay  
FROM 
    BTST

Notes - this will be slow for lots of records - if you what this to be permanent then create an update query using the same function.

注释 - 这对于大量记录来说会很慢 - 如果您认为这是永久性的，那么使用相同的函数创建更新查询。

EDIT: to do an Update:

编辑：做一个更新：

UPDATE BTST
    SET ShipToPlant = StripChars(ShipToPlant, "abcdefghijklmnopqrstuvwxyz0123456789")

回答by shahkalpesh

SELECT 
IIF
(
    Instr(1,ShipToPlant , "#") > 0 
    OR Instr(1,ShipToPlant , "/") > 0 
    OR Instr(1,ShipToPlant , "-") > 0, ""
    , ShipToPlant 
)
FROM BTST

回答by shahkalpesh

All - I wound up nesting the REPLACE() function in two separate queries. Since there's upwards of 35 non-alphanumeric characters that I needed to replace and Access limits the complexity of the query to somewhere around 20 nested functions, I merely split it into two processes. Somewhat clunky, but it worked. Should have followed the KISS principle in this case. Thanks for your help!

全部 - 我最终将 REPLACE() 函数嵌套在两个单独的查询中。由于我需要替换多达 35 个非字母数字字符，而 Access 将查询的复杂性限制在大约 20 个嵌套函数的范围内，因此我只是将其拆分为两个进程。有点笨重，但它奏效了。在这种情况下应该遵循 KISS 原则。谢谢你的帮助！

回答by rwtwm

I know this is a really old question, but I stumbled over it whilst looking for a solution to this problem, but ended up using a different approach.

我知道这是一个非常古老的问题，但我在寻找解决此问题的方法时偶然发现了它，但最终使用了不同的方法。

The field that I wish to update is called 'Customers'. There are 20-odd accented characters in the 'CustName' field for which I wish to remove the diacritics - so (for example) ? > a.

我希望更新的字段称为“客户”。“CustName”字段中有 20 多个重音字符，我希望为其删除变音符号 - 所以（例如）？> 一个。

For each of these characters I created a new table 'recodes' with 2 fields 'char' and 'recode'. 'char' contains the character I wish to remove, and 'recode' houses the replacement.

对于这些字符中的每一个，我创建了一个新表“重新编码”，其中包含 2 个字段“字符”和“重新编码”。'char' 包含我希望删除的字符，而 'recode' 包含替换字符。

Then for the replace I did a full outer join inside the update statement

然后对于替换我在更新语句中做了一个完整的外连接

UPDATE Customers, Recodes SET Customers.CustName = Replace([CustName],[char],[recode]);

This has the same effect as nesting all of the replace statements, and is a lot easier to manage.

这与嵌套所有替换语句具有相同的效果，并且更易于管理。

回答by airwolf09

This query grabs the 3 first characters and replace them with Blanks

此查询获取前 3 个字符并将它们替换为空白

Example: BO-1234
Output: 1234

示例：BO-1234
输出：1234

BO: IIf(IsNumeric(Left([sMessageDetails],3)),[sMessageDetails],Replace([sMessageDetails],Left([sMessageDetails],3),""))