CODEWITHSUNDEEP
sqlms-accessuppercaselowercase
Chase
Chase

Reputation: 584

Delete Words w/Upper And Lowercase Letters or Only Lowercase Letters

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

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

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

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

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

Upvotes: 2

Views: 1479

Answers (1)

HansUp
HansUp

Reputation: 97131

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

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

Upvotes: 1

Related Questions