"Query too Complex"：MS 访问函数以替换查询中的文本(嵌套替换())

Question

目的：在MS访问中使用SQL查询来找到与长文本字段中匹配特定关键字的所有记录

我正在尝试查询MS Access DB中的所有记录，该记录在字段中的特定关键字列表中具有匹配。关键字如下：

ain，atin，ckd，aki，arf

我遇到的问题是该字段是一个自由文本输入字段，因此数据的格式遍布整个地方，而我正在搜索的关键字通常会出现在其他全长的中间单词(即ain匹配"痛苦"，"再次"等(，而我只想在严格的单词上包含匹配，这些单词严格属于关键字(即" ain"，" aki"(。

我正在使用的想法就是简单地包含将以以下格式达到以下格式的匹配：field_name，例如' * ain *'。因此，基本上只包括在关键字之前和之后具有空间以限制限制数量的匹配项结果集中出现的误报。

我尝试编写一个将数据标准化的SQL查询，以便所有其他出现的字符("。"，"！"，"？"，"，"等等"将被替换为空间字符(即" ain！"将被替换(field_name，"！"，"(=" ain"(，认为这仅应包括包含关键字的单词。在尝试在查询中运行我非常长的替换语句时，我正在收到"查询太复杂"消息。嵌套替换如下：

UCASE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.REF_CONTENT_NM,chr(13)," "),chr(10)," "),"`"," "),"~"," "),"!"," "),"@"," "),"#"," "),"$"," "),"%"," "),"^"," "),"&"," "),"*"," "),"("," "),")"," "),"-"," "),"_"," "),"="," "),"+"," "),"["," "),"{"," "),"]"," "),"}"," "),";"," "),":"," "),","," "),"<"," "),"."," "),">"," "),"/"," "),"?"," "),""," "),"|"," "),""""," ")) like "* AIN *"

我相信，解决方法是创建一个可以在SQL语句中引用的自定义函数，但是我不确定如何完成此操作。因此，从本质上讲，我正在寻找有关如何评估如何将文本归一化的解决方案的指导，而不必遇到"查询太复杂消息"中的嵌套替换语句。我觉得有一个简单的解决方案，我只是在这里没有看到，因此可以非常赞赏指导！

Answer 1

编写自定义函数以做到这一点的主要技巧是使用ParamArray

正确的

这是一个执行多个替换的小函数：

Public Function ReplaceMultiple(strInput As String, strReplace As String, ParamArray Find() As Variant) As String
    Dim i As Long
    ReplaceMultiple = strInput
    For i = LBound(Find) To UBound(Find)
        ReplaceMultiple = Replace(ReplaceMultiple, Find(i), strReplace)
    Next
End Function

实施：

ReplaceMultiple(a.REF_CONTENT_NM, " ", chr(13), chr(10), "`", "etc....")

但是，您可能需要考虑完全更改逻辑，例如保留应替换的字符表。我记得有关最大参数数量在20-30左右的事情，因此您可能需要两次使用ReplaceMultiple。

如果您只想替换没有空间的所有字符串的所有内容，则可以尝试以下小函数：

Public Function ReplaceNonAlphanumeric(str As String) As String
    If str = "" Then Exit Function
    Dim i As Long
    For i = 1 To Len(str)
        If Mid(str, i, 1) Like "[0-9A-z]" Then
            ReplaceNonAlphanumeric = ReplaceNonAlphanumeric & Mid(str, i, 1)
        Else
            ReplaceNonAlphanumeric  = ReplaceNonAlphanumeric  & " "
        End If
    Next
End Function