选择中删除非数字字符。

Question

在MS Access 2007项目报告中，我有以下（已编辑）查询：

SELECT SomeCol FROM SomeTable

问题在于，SomeCol可能包含一些不可见字符。例如，我看到一个结果返回为123456，但是SELECT LEN(SomeCol)返回的是7。当我将结果复制到Notepad++中时，它显示为?123456。

该列设置为TEXT。我无法控制这个数据类型，所以我不能更改它。

如何修改我的SELECT查询以去除任何非数字内容。我怀疑正则表达式是正确的选择…或者是否有CAST或CONVERT函数？

- Danny Beckett

Answer 1

你提到要使用正则表达式来实现这个功能。Access的数据库引擎确实不直接支持正则表达式。但是，看起来你愿意在查询中使用VBA自定义函数...而且UDF可以使用正则表达式方法。这种方法应该简单、容易，并且比逐个遍历输入字符串并仅将要保留的字符存储在新输出字符串中的方法更快。

Public Function OnlyDigits(ByVal pInput As String) As String
    Static objRegExp As Object

    If objRegExp Is Nothing Then
        Set objRegExp = CreateObject("VBScript.RegExp")
        With objRegExp
            .Global = True
            .Pattern = "[^\d]"
        End With
    End If
    OnlyDigits = objRegExp.Replace(pInput, vbNullString)
End Function

以下是将函数应用于Immediate窗口的示例，其中“x”字符作为您不可见字符的代理。(未包括在“digits”字符类中的任何字符都将被丢弃。)

? OnlyDigits("x1x23x")
123

如果那是你想要的输出结果，只需在你的查询中使用该函数。

SELECT OnlyDigits(SomeCol) FROM SomeTable;

- HansUp

---

看过您的答案后，我同意这个更好，因为它不需要每个字符都被循环遍历（当每个字符串有多达9个字符且共有9500行时，这很有用）。谢谢！ - Danny Beckett

Answer 2

在Access中没有正则表达式，至少在SQL中没有。如果你尝试使用VBA，可以在SQL语句中使用自定义的StripNonNumeric VBA函数。

例如：SELECT StripNonNumeric(SomeCol) as SomeCol from SomeTable

Function StripNonNumeric(str)
      keep = "0123456789"
      outstr = ""
      For i = 1 to len(str)
          strChar = mid(str,i,1)
          If instr(keep,strChar) Then
              outstr = outstr & strChar
          End If
      Next
      StripNonNumeric = outstr
  End Function

- RichardTheKiwi

---

这非常完美！就是给偶然遇到这个问题的任何人。只需转到“创建”选项卡，找到宏一栏，按箭头并选择模块。将此代码复制到新模块中，保存并关闭。然后使用 SELECT StripNonNumeric(SomeCol) FROM SomeTable。非常高兴，谢谢Richard！ - Danny Beckett

正如您所说，SQL中没有可用的RegEx，但可以在Access VBA中使用。虽然我不认为这会增加或减少运行时间，但您可以稍微更改一下，放弃keep字符串，改用内置的IsNumeric函数。 - Matt Donnan

Answer 3

你可以在一个查询中完成所有操作，将这个问题与你的上一个问题结合起来，你就得到了：

SELECT IIf(IsNumeric([atext]),
           IIf(Len([atext])<4,Format([atext],"000"),
               Replace(Format(Val([atext]),"#,###"),",",".")),
           IIf(Len(Mid([atext],2))<4,Format(Mid([atext],2),"000"),
               Replace(Format(Val(Mid([atext],2)),"#,###"),",","."))) AS FmtNumber
FROM Table AS t;

- Fionnuala

---

“SELECT VAL(SomeCol) FROM SomeTable WHERE SomeCol = '123456'” 返回的是0（在1之前存在某些不可见字符 - 只有WHERE本身可以正常工作） - Danny Beckett

第一个 IIf(IsNumeric([atext] 表示如果文本没有奇数起始字符，则将 val 应用于文本，如果文本具有奇数非数字起始字符，则将 val 应用于 Mid([atext],2)，即删除起始字符。以上 SQL 已经过测试。 - Fionnuala

如果字符不在位置1怎么办？那该怎么办呢？它可能在字符串的中间，例如： - Danny Beckett

Answer 4

Public Function fExtractNumeric(strInput) As String
' Returns the numeric characters within a string in
' sequence in which they are found within the string
Dim strResult As String, strCh As String
Dim intI As Integer
If Not IsNull(strInput) Then
    For intI = 1 To Len(strInput)
        strCh = Mid(strInput, intI, 1)
        Select Case strCh
            Case "0" To "9"
                strResult = strResult & strCh
            Case Else
        End Select
    Next intI
End If
fExtractNumeric = strResult

结束函数

- Masum