问题描述:

I have a data column that has a heading value with multiple levels, where I only want the first three levels, but I cannot figure out how to get the parsed value?

I was reading this and it shows how to use create a function to return a boolean for the condition, but how would I create a function that would return a parsed value?

This is the Regular Expression that I think I need.

^(\d.\d.\d)

I'm looking for something that would change 1.2.3.4.5. to 1.2.3 and similar for any other header I have that has more than three levels.

Ideally, I'd like to be able to put it into my Query Design as a Field Expression, but I'm not sure how I would do that.

网友答案:

I assumed your input values could have more than one digit between the dots. In other words, I think you want this ...

? RegExpGetMatch("1.2.3.4.5.", "^(\d+\.\d+\.\d+).*", 1)
1.2.3
? RegExpGetMatch("1.27.3.4.5.", "^(\d+\.\d+\.\d+).*", 1)
1.27.3

If that is the correct behavior, here is the function I used.

Public Function RegExpGetMatch(ByVal pSource As String, _
    ByVal pPattern As String, _
    ByVal pGroup As Long) As String

    'requires reference to Microsoft VBScript Regular Expressions
    'Dim re As RegExp
    'Set re = New RegExp
    'late binding; no reference needed
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    re.Global = True
    re.Pattern = pPattern
    RegExpGetMatch = re.Replace(pSource, "$" & pGroup)
    Set re = Nothing
End Function

See also this answer by KazJaw. His answer taught me how to select the match group with RegExp.Replace.

In a query run within an Access session, you could use the function like this:

SELECT
    RegExpGetMatch([Data Column], "^(\d+\.\d+\.\d+).*", 1) AS parsed_value
FROM YourTable;

Note however a custom VBA function is not usable for queries run from outside an Access session.

网友答案:

Try changing your RegEx to ^(\d\.\d\.\d). You need to escape the . since it has a special meaning in RegExp.

相关阅读:
Top