VBA或PostgreSQL：从数学方程字符串中删除不需要的括号
问题描述：
我正在寻找从数学方程字符串中删除数学上不需要的括号。我需要这样做，最好是在PostgreSQL 6或VBA中。VBA或PostgreSQL：从数学方程字符串中删除不需要的括号
例如，我有一个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
： Remove redundant parentheses from an arithmetic expression
我将代码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://*.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
它需要大量的清理，可能有一些测试。我会给予答复，不管是最好的改进还是不同的解决方案，都会更好。
UPDATE： 忘记这个功能：
Public Function IsAlpha(strValue As String) As Boolean
IsAlpha = strValue Like WorksheetFunction.Rept("[a-zA-Z]", Len(strValue))
End Function
这太辛苦了正则表达式。你需要解析表达式。 –
...另外，我不确定你的例子是否正确。 '/'具有（通常）比'+更高的优先级，所以在本例中，您也删除了“需要”的括号。 – pozs
@pozs - 对不起。我纠正了它。谢谢！另外，它不一定是正则表达式。 – mountainclimber