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

问题描述

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

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.

我正在尝试与上述帖子的发起人做同样的事情，但使用不同的表格和不同的字段.假设表BTST"中的以下字段ShiptoPlant"有三个记录(我的表实际上有数千条记录)...

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)...

表名:BTST

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

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

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

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

我不断收到消息(错误 3075)...

I keep getting the message (Error 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!

从评论中添加的背景信息

我从 2008 日历年的 14 家供应商处收集了订单项发票级别的数据.我正在尝试规范供应商提供给我们的植物名称.

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.

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

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

Signode Service

Signode Service
Signode - Service.
SignodeSvc
SignodeService

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

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...

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

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," ","");

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

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

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,

128 Brookview Drive
128 Brookview Lane

通过取出字母数字并做

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

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

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?

推荐答案

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

You may wish to consider a User Defined Function (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

这篇关于如何在 Access SQL 中替换多个字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！