sql - 微软 Access SQL : use update and replace to remove multiple spaces with a single one

Question

在 MS Access 中我尝试使用:

UPDATE Table SET FieldName= REPLACE(FieldName, '\s+', '\s');

从字段中删除多个空格，但它不起作用。

Answer 1

如问题评论中所述，Replace() 函数不支持正则表达式。但是，您可以使用以下 VBA 代码实现您的目标:

Option Compare Database
Option Explicit

Sub RemoveMultipleSpaces()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Do While DCount("FieldName", "TableName", "FieldName LIKE ""*  *""") > 0
        cdb.Execute "UPDATE TableName SET FieldName = Replace(FieldName,""  "","" "")"
    Loop
    Set cdb = Nothing
End Sub

编辑回复:评论

或者，您可以使用以下使用正则表达式的代码来查找替换候选者:

Option Compare Database
Option Explicit

Public Function RegexReplace( _
        originalText As Variant, _
        regexPattern As String, _
        replaceText As String, _
        Optional GlobalReplace As Boolean = True) As Variant
    Dim rtn As Variant
    Dim objRegExp As Object  ' RegExp

    rtn = originalText
    If Not IsNull(rtn) Then
        Set objRegExp = CreateObject("VBScript.RegExp")
        objRegExp.Pattern = regexPattern
        objRegExp.Global = GlobalReplace
        rtn = objRegExp.Replace(originalText, replaceText)
        Set objRegExp = Nothing
    End If
    RegexReplace = rtn
End Function

使用示例:

RegexReplace("This is     a test.","\s+"," ")

返回

This is a test.

您可以在这样的查询中使用它:

UPDATE TableName SET FieldName = RegexReplace(FieldName,'\s+',' ')