VBA or PostgreSQL: remove unneeded parentheses from a mathematical equation string

Question

I'm looking to remove mathematically unneeded parentheses from a mathematical equation string. I need to do this either, and preferably, in PostgreSQL 6 or VBA.

For example, I have the following string value in a PostgreSQL database:

PercentileRank((([bp47244]+([bp47229][ttm]))/(AvgAeTe([bp48918]))))

And I need it to look like this (edited/corrected):

PercentileRank(([bp47244]+[bp47229][ttm])/AvgAeTe([bp48918]))

I'd prefer a function or query in PostgreSQL, but a VBA solution could work.

Note PercentileRank() and AvgAeTe() are functions. This [bp47244] and [bp47229][ttm] each represent single numbers/variables, but they could be expressed in any way, like [abc123] and [xyz321][ttm]. I see a lot of examples out there, but I don't see one using PostgreSQL or VBA that works for me, so I thought it would be a good question.

Of course I am looking for a general solution that can be applied to any equation.

I'm working on this now, so if I find an answer before one is posted here, I'll share; however, I am not good at regex (not that the solution has to use regex).

Thanks!

UPDATE: I'm working off this logic:

Let L be operator immediately left of the left parenthesis, or nil
Let R be operator immediately right of the right parenthesis, or nil
If L is nil and R is nil:
  Redundant
Else:
  Scan the unparenthesized operators between the parentheses
  Let X be the lowest priority operator
  If X has lower priority than L or R:
    Not redundant
  Else:
    Redundant

from this link: Remove redundant parentheses from an arithmetic expression

I'll code something up in VBA that follows this logic and post an answer.

That's too hard for a regular expression. You need to parse the expression. — Laurenz Albe, May 26 '17 at 15:10
... also, I'm not sure that your example is correct. `/` has (usually) higher precedence than `+`, so in this example, you removed a "needed" parenthesis too. — pozs, May 26 '17 at 16:05
@pozs - Sorry about that. I corrected it. Thanks! Also, it doesn't have to be regex. — mountainclimber11, May 26 '17 at 16:52
@Parfait - Yes, that is the strategy I am taking, but its still not that simple. — mountainclimber11, May 26 '17 at 17:34

mountainclimber11 · Accepted Answer · 2017-05-31T15:02:52.983

This seems to work for my situation:

Function RemoveParens(s As String) As String
'remove unecessary parentheses
'exponents not implemented
'mathematical brackets are not implmented (it is assumed that only parentheses are used to create mathematical order)
    'brakets are assumed to identify a variable or calculation on a variable
        '[bp47229][ttm] -> one value/variable; [xyz123] -> one value/variable
'logic based on Antti Huima's answer:
    'https://stackoverflow.com/questions/44203517/vba-or-postgresql-remove-unneeded-parentheses-from-a-mathematical-equation-stri


's = "PercentileRank((([bp47244]+([bp47229][ttm]))/(AvgAeTe([bp48918]))))"
's = "PercentileRank(2*(1+3)(5*4))"
If InStr(1, s, "^") > 0 Then
    msgbox "Exponents are not implemented in RemoveParens"
End If

ReDim arS(1 To Len(s)) As String
Dim i As Integer
For i = 1 To Len(s)
    arS(i) = Mid(s, i, 1)
Next i
Dim iCnt As Integer
iCnt = 0
Dim iLen As Integer
iLen = Len(s)
Dim sTmp As String
Dim bRemove As Boolean
bRemove = False
Dim sLfOpr As String
Dim sRtOpr As String
Dim iCntBtwn As Integer
Dim sLast As String
'loop through chars
Do
    iCnt = iCnt + 1
    sTmp = Mid(s, iCnt, 1)

    If sTmp = "(" Then
        if iCnt - 1 <= 0 then
            sLfOpr = ""
        else
            sLfOpr = Mid(s, iCnt - 1, 1)
        end if
        'in case we have "5(...) or (...)(...)
        If IsNumeric(sLfOpr) Or sLfOpr = ")" Then
            sLfOpr = "*"
        End If

        'if it isn't an oper then clear it
        If sLfOpr <> "+" _
            And sLfOpr <> "-" _
            And sLfOpr <> "/" _
            And ((Not IsAlpha(sLfOpr) = True) Or (Not Mid(s, iCnt, 1) = "(")) _
            And sLfOpr <> "*" _
            Then
            sLfOpr = ""
        End If

        'find the matching paren to the right of LfOpr
        Dim iCntR As Integer
        iCntR = iCnt

        Dim iCntParen As Integer
        iCntParen = 1
        Dim sTmpR As String
        sTmpR = ""
        Do
            iCntR = iCntR + 1
            sTmpR = Mid(s, iCntR, 1)

            If sTmpR = "(" Then
                iCntParen = iCntParen + 1
            ElseIf sTmpR = ")" Then
                    iCntParen = iCntParen - 1
            End If

            'we found the close paren that matches the open paren
            If iCntParen = 0 Then
                sRtOpr = Mid(s, iCntR + 1, 1)
                'in case we have "(...)5 or (...)(...)
                If IsNumeric(sRtOpr) Or sRtOpr = "(" Then
                    sRtOpr = "*"
                End If
                If sRtOpr <> "+" _
                    And sRtOpr <> "-" _
                    And sRtOpr <> "/" _
                    And ((Not IsAlpha(sRtOpr) = True) Or (Not Mid(s, iCntR, 1) = "(")) _
                    And sRtOpr <> "*" _
                    Then
                    sRtOpr = ""
                End If
                If sRtOpr = "" And sLfOpr = "" Then
                    arS(iCnt) = ""
                    arS(iCntR) = ""
                     'go to the next overall open paren
                     Exit Do
                Else
                    ' ------------ search btwn parens -------------------
                    Dim iCntParenOp As Integer
                    Dim iCntParenCl As Integer
                    iCntParenOp = 0
                    iCntParenCl = 0
                    Dim sTmpB As String
                    sTmpB = ""
                    Dim sLowOpr As String
                    sLowOpr = ""
                    Dim iCntRLw As Integer
                    iCntRLw = iCnt
                    Dim bInSub As Boolean
                    bInSub = False
                    Dim bNoOpr As Boolean
                    bNoOpr = True
                    'loop through chars between the two parens
                    For i = iCnt + 1 To iCntR
                        iCntRLw = iCntRLw + 1
                        sTmpR = Mid(s, iCntRLw, 1)
                        If sTmpR = "(" Then
                            iCntParenOp = iCntParenOp + 1
                            bInSub = True
                        ElseIf sTmpR = ")" Then
                                iCntParenCl = iCntParenCl + 1
                                If bInSub = True And iCntParenCl = iCntParenOp Then
                                    bInSub = False
                                End If
                        End If
                        'we found the close paren that matches the open paren
                            'and we are not in a nested/sub paren
                        If bInSub = False Then
                            'in case we have "(...)5 or (...)(...)
                            If (IsNumeric(sTmpR) And Mid(s, iCntRLw + 1, 1) = "(") Or (sTmpR = "(" And Mid(s, iCntRLw + 1, 1) = "(") Then
                                sTmp = "*"
                            End If
                            'it is an operator
                            If sTmpR = "+" _
                                Or sTmpR = "-" _
                                Or sTmpR = "/" _
                                Or ((IsAlpha(sTmpR) = True) And (Mid(s, iCntRLw + 1, 1) = "(")) _
                                Or sTmpR = "*" _
                                Or bNoOpr = True _
                                Then

                                    'see if sLowROpr operater has lower priority than sLfOpr and sRtOpr
                                    If Not IsLowerPri(sTmpR, sRtOpr, sLfOpr) Then
                                            arS(iCnt) = ""
                                            arS(iCntR) = ""
                                            Exit For
                                    End If
                                    bNoOpr = False
                            End If

                        End If
                    Next i
                End If
                Exit Do 'always stop loop if iCntParen = 0
            End If

        Loop While iCntR <> iLen

    End If
Loop While iCnt <> iLen

Dim sOut As String
For i = LBound(arS) To UBound(arS)
    sOut = sOut & arS(i)
Next i
'Debug.Print s
RemoveParens = sOut
End Function
Function IsLowerPri(sTestOpr As String, sRtOpr As String, sLfOpr As String) As Boolean
'exponents not implemented yet
Dim iTestOpr As Integer
Dim iRtOpr As Integer
Dim iLfOpr As Integer
iTestOpr = 1
If sTestOpr = "+" Or sTestOpr = "-" Then
    iTestOpr = 1
ElseIf sTestOpr = "*" Or sTestOpr = "/" Then
    iTestOpr = 2
ElseIf IsAlpha(sTestOpr) And sTestOpr <> "" Then
    iTestOpr = 3
End If

If sRtOpr = "+" Or sRtOpr = "-" Then
    iRtOpr = 1
ElseIf sRtOpr = "*" Or sRtOpr = "/" Then
    iRtOpr = 2
ElseIf IsAlpha(sRtOpr) And sRtOpr <> "" Then
    iRtOpr = 3
End If

If sLfOpr = "+" Or sLfOpr = "-" Then
    iLfOpr = 1
ElseIf sLfOpr = "*" Or sLfOpr = "/" Then
    iLfOpr = 2
ElseIf IsAlpha(sLfOpr) And sLfOpr <> "" Then
    iLfOpr = 3
End If

If iTestOpr < iRtOpr Or iTestOpr < iLfOpr Then
    IsLowerPri = True
Else
    IsLowerPri = False
End If
End Function

It needs a lot of clean-up and probably some testing. I will give answer credit to whomever posts the best improvement or a different solution all together that is better.

UPDATE: Forgot this function:

Public Function IsAlpha(strValue As String) As Boolean
    IsAlpha = strValue Like WorksheetFunction.Rept("[a-zA-Z]", Len(strValue))
End Function

VBA or PostgreSQL: remove unneeded parentheses from a mathematical equation string

1 Answers1