Query to parse a field and display it

Question

I have a table with values

Errors:
X_11;SR_4;D_11;SR_2
SR_4;T_22
E_18; E_28; SR_3; 
E_28; SR_3; 
SR_2;SR_4

I need to put in a query to parse the values so that anything with SR comes up so I do like "*SR*" but in the output I need to display only this:

Errors:
SR_4;SR_2
SR_4
SR_3 
SR_3 
SR_2;SR_4

I would like this in query with many fields other than this one ... instead of VBA. I am using MS Access 2010, I am guessing some type of parsing with each field being separated with ";" that will only capture SR ones?

It seems you only want to filter errors with values `SR_#` (where `#` is a digit). Is `#` a single digit always? — Barranka, Apr 20 '15 at 23:31
do you know beforehand which errors (or error patterns) you want to ommit? — Barranka, Apr 20 '15 at 23:33
are the error codes unique per row? (i.e. you don't have `SR_2` more than once in each row) — Barranka, Apr 20 '15 at 23:45
there is only one error type per row so it will only have 1 SR_2 ... and another SR_4 but never 2 SR_2's. Currently only 1 digit not 2 digits after SR_# — Edgar, Apr 20 '15 at 23:49

score 1 · Accepted Answer · edited May 23 '17 at 12:14

I think regular expressions might be a way to go.

In VBA, you need to enable the reference to "Microsoft VBScript Regular Expressions 5.5". This question and its accepted answer has a detailed descrpition on what are Regular Expressions and how to enable them in your project (it's for Excel, but for Access is the same route).

Once you have the reference enabled, this little function will give you a "clean" string:

Public Function filterString(str As String)
    Dim re As RegExp, obj As Object, x As Variant, first As Boolean
    Set re = New RegExp
    With re
        .Global = True
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "SR_[0-9]" ' This will match the string "SR_" 
                              ' followed by a digit
    End With

    filterString = ""
    first = True

    If re.Test(str) Then
        Set obj = re.Execute(str)
        For Each x In obj
            If first Then
                first = False
            Else
                filterString = filterString & ";"
            End If
            filterString = filterString & x
        Next x
    End If
End Function

If you test it you'll see that the result is:

filterString("X_11;SR_4;D_11;SR_2")
  SR_4;SR_2

which is the result you want.

Now, a simple select query will give you what you need:

select filterString([Errors]) as err
from [yourTable]
where [yourTable].[Errors] like '*sr*'

Hope this helps

error "user defined type not defined" RegExp issue .... i have access 2010 any other variable can be used? — Edgar, Apr 21 '15 at 00:48
@Edgar Happy to help. If you find this answer useful, upvote it (and/or accept it) — Barranka, Apr 21 '15 at 16:01

HansUp · Answer 2 · 2015-04-21T01:35:00.243

I think you can get what you need by splitting your input string into an array and then using the Filter function to create a second array which includes only the SR_ matches from the first array. Finally Join the second array to produce your output string which contains the matches.

Public Function filterString(ByVal pInput As String) As String
    Dim array1() As String
    Dim array2() As String
    array1 = Split(Replace(pInput, " ", vbNullString), ";")
    array2 = Filter(array1, "SR_")
    filterString = Join(array2, ";")
End Function

Compared to a regular expression approach, this function is more concise. I find the logic simpler. And it does not require setting a reference.

Notice also it will accommodate SR codes which include more than a single digit (in case that eventually becomes a requirement). For example:

? filterString("X_11;SR_4;D_11;SR_234")
SR_4;SR_234

You could use that function in a query in the same way @Barranka suggested:

SELECT filterString(y.Errors) AS sr_codes
FROM [yourTable] AS y
WHERE y.Errors Like '*sr*';

Query to parse a field and display it

2 Answers2