如何替换多个字符在访问SQL？ [英] How to Replace Multiple Characters in 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.

我试图做同样的事情上面张贴的鼻祖，但使用不同的表，不同领域。比方说，下面的字段 ShiptoPlant 表 BTST 有三个记录（我的表实际上有数千记录）...

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.

任何帮助，您可以提供将大大AP preciated！

Any help you could provide would be greatly appreciated!

编辑：从注释中加入背景资料

我已经收集了来自我们每一个14的供应商，为2008年日历年线项目的发票级别的数据。我想正常化是由我们的供应商给我们的植物名称。

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 on our master list could be called by suppliers

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个新的排列出现在我们行项目的发票数据 - 这可能会重新present成千上万的发票记录。我试图构建一个快速和肮脏的方式来确定的名称的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

，采取了字母数字和做

By taking out alphanumerics and doing

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

和改变的字符数，直到一个匹配的发票数据和主厂房上市之间存在（这两个表包含植物，地址，城市和国家领域），就可以最终找到一个匹配。当然，当你开始萎缩下来的字符数你是左 ING，精度变得大打折扣。我在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

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