我希望从数学方程字符串中删除数学上不需要的括号。我需要这样做，最好是在PostgreSQL 6或VBA中。
例如，我在PostgreSQL数据库中有以下字符串值：
PercentileRank((([bp47244]+([bp47229][ttm]))/(AvgAeTe([bp48918]))))
我需要它看起来像这样(编辑/更正)：
PercentileRank(([bp47244]+[bp47229][ttm])/AvgAeTe([bp48918]))
我更喜欢PostgreSQL中的函数或查询，但是VBA解决方案可以工作。
注意PercentileRank()和AvgAeTe()是函数。这个[bp47244]和[bp47229][ttm]分别表示单个数字/变量，但它们可以用任何方式表示，如[abc123]和[xyz321][ttm]。我看到很多例子，但我没有看到一个使用PostgreSQL或VBA的例子适合我，所以我认为这将是一个很好的问题。
当然，我正在寻找一个可以应用于任何方程的通用解决方案。
我现在正在研究这个问题，所以如果我在这里发布之前找到答案，我会分享;但是，我不擅长正则表达式(并不是说解决方案必须使用正则表达式)。
谢谢！
更新： 我正在研究这个逻辑：
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
从此链接： 从算术表达式中删除多余的括号
我将在遵循此逻辑的 VBA 中编写一些代码并发布答案。
这似乎适用于我的情况：
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
它需要大量的清理，可能需要一些测试。我会把答案归功于发布最佳改进或更好的不同解决方案的人。
更新： 忘记了这个功能：
Public Function IsAlpha(strValue As String) As Boolean
IsAlpha = strValue Like WorksheetFunction.Rept("[a-zA-Z]", Len(strValue))
End Function