In MS Access, How do I extract multiple values in a single column?-CodePudding

How do I parse multiple values out of a single column? The problem is that multiple values are surrounded by extraneous (for my purposes) data. Example:

Select * from my_table:
Fname   Lname    Data
Fred    Smith    @3aXXXX;Name:AA;@43deXXXX;Name:BB;@5433ed9;NAME:ABC;*#!XXXXXXXX;NAME:MyPetDog;@#IDXXXX

For the Data column, I want to extract all the values following the "Name:" fields from the column. That would be the text following "Name:" and preceding ";". In the example above:

Select Fname, Lname, [DATA] from my_table

Fname   Lname    [*Parsed* DATA]
Fred    Smith    AA,BB, ABC, MyPetDog

Solving the above Would be a tremendous help. However, what I would really like to do a lookup/replace (SWITCH, etc.) each of the values returned from the string:

Fname   Lname    [Translated DATA]
Fred    Smith    Airport, Bus Station, Restaurant, FIDO

I apologize for using notional data. My actual script (on another network) involves several table joins to get to the column DATA. I just can't figure out how to extract the specific values from this large string (other that to extract the full data set and use AWk or MS Excel to cleanup the data afterwards).

Appreciate any assistance or tips on solving this.

Kevin L.

CodePudding user response：

I would recommend creating a small VBA function that takes the "ugly" data and splits it out using the Split function. Something like:

Public Function fSplitData(strData As String) As String
    Dim aData() As String
    Dim lngLoop1 As Long
    aData = Split(strData, ";")
    For lngLoop1 = LBound(aData) To UBound(aData)
        If Left(aData(lngLoop1), 5) = "Name:" Then
            fSplitData = fSplitData & Mid(aData(lngLoop1), 6) & ","
        End If
    Next lngLoop1
    If Right(fSplitData, 1) = "," Then fSplitData = Left(fSplitData, Len(fSplitData) - 1)
End Function

This gives the required output of:

AA,BB,ABC,MyPetDog

And, rather than just concatenating the extracted value, you could use a recordset to get the data from a lookup table:

Public Function fSplitData(strData As String) As String
    Dim db As DAO.Database
    Dim rsLookup As DAO.Recordset
    Dim strLookup As String
    Dim aData() As String
    Dim lngLoop1 As Long
    aData = Split(strData, ";")
    Set db = CurrentDb
    For lngLoop1 = LBound(aData) To UBound(aData)
        If Left(aData(lngLoop1), 5) = "Name:" Then
            strLookup = Mid(aData(lngLoop1), 6) & ","
            Set rsLookup = db.OpenRecordset("SELECT LookupData FROM tblName WHERE RawData='" & strLookup & "';")
            If Not (rsLookup.BOF And rsLookup.EOF) Then
                fSplitData = fSplitData & rsLookup!LookupData & ","
            End If
        End If
    Next lngLoop1
    If Right(fSplitData, 1) = "," Then fSplitData = Left(fSplitData, Len(fSplitData) - 1)
End Function

Regards,

CodePudding user response：

Thanks to @Applecore, I have a almost working solution (though not likely the best solution):

SELECT Fname,
       Lname,
       SWITCH(data like "*AA*","Airport")&", "&
       SWITCH(data like "*BB*", "Bus Station")&", "&
       SWITCH(data like "*ABC*", "Restaurant")&", "&
       SWITCH(data like "*MyPetDog*","FIDO") as DATA

Fname   Lname    [Translated DATA]
Fred    Smith    Airport, Bus Station, Restaurant, FIDO

The only problem is,if a value is NOT there, then I get extra commas. For example, if BB is the only value present, then I get:

Fname   Lname    [Translated DATA]
Fred    Smith    , Bus Station, ,