删除带有大写和小写字母或仅小写字母的单词

Objective: I have an Access table with a column Item_Desc. I want this column to only contain the brand names of clothing. The brand names are always written in ALL CAPS, e.g., "RALPH LAUREN".

目标:我有一个带有Item_Desc列的Access表。我希望此专栏仅包含服装的品牌名称。品牌名称始终以全部大写字母书写,例如“RALPH LAUREN”。

Problem: The Item_Desc column is populated with both brand names and type of article of clothing (and color and etc.). Thus, the Item_Desc column may contain, for example, "RALPH LAUREN" (good) or possibly "RALPH LAUREN Coat black" (bad).

问题:Item_Desc列中填充了品牌名称和服装类型(以及颜色等)。因此,Item_Desc列可以包含例如“RALPH LAUREN”(好)或可能包含“RALPH LAUREN Coat black”(坏)。

Question: Is there a query I can run that will delete the words from Item_Desc that either: 1) Begin with an uppercase letter followed by lowercase letters (e.g., "Coat"), or 2) Are written solely in lowercase (e.g., "black").

问题:是否有可以运行的查询将删除Item_Desc中的单词:1)以大写字母开头,后跟小写字母(例如“Coat”),或2)仅以小写字母书写(例如,“黑色”)。

Thus, "RALPH LAUREN Coat black" would end up as just "RALPH LAUREN" in the Item_Desc column.

因此,“RALPH LAUREN Coat black”最终将成为Item_Desc列中的“RALPH LAUREN”。

My table name is "Brand".

我的表名是“品牌”。

Lastly, I have no control over the report generated, so I unfortunately can't import Brand, Style, and Color into separate columns to begin with (I don't think!).

最后,我无法控制生成的报告,所以我很遗憾无法将Brand,Style和Color导入到单独的列中(我不认为!)。

1 个解决方案

#1

You can use a VBA function based on a regular expression to accomplish your goal.

您可以使用基于正则表达式的VBA函数来实现目标。

Here is one such function tested in the Immediate window with your sample inputs:

以下是使用示例输入在立即窗口中测试的一个此类函数:

? OnlyUpperCaseWords("RALPH LAUREN")
RALPH LAUREN
? OnlyUpperCaseWords("RALPH LAUREN Coat black")
RALPH LAUREN

You could use that function in an UPDATE query to change your stored Item_Desc values:

您可以在UPDATE查询中使用该函数来更改存储的Item_Desc值:

UPDATE Brand AS b
SET b.Item_Desc = OnlyUpperCaseWords(b.Item_Desc)
WHERE b.Item_Desc Is Not Null;

This is the function ...

这是功能......

Public Function OnlyUpperCaseWords(ByVal strSource As String) As String
    Static re As Object

    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        re.Global = True
        re.IgnoreCase = False
        re.Pattern = "\s*\b[A-Za-z]*[a-z]+[A-Za-z]*\b\s*"
    End If
    'OnlyUpperCaseWords = re.Replace(strSource, vbNullString)
    OnlyUpperCaseWords = Trim(re.Replace(strSource, " "))
End Function

#1