去除 SELECT 中的非数字字符 [英] Strip out non-numeric characters in SELECT

问题描述

在 MS Access 2007 项目报告中，我有以下(已编辑)查询:

In an MS Access 2007 project report, I have the following (redacted) query:

SELECT SomeCol FROM SomeTable

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

The problem is, that SomeCol apparently contains some invisible characters. For example, I see one result returned as 123456 but SELECT LEN(SomeCol) returns 7. When I copy the result to Notepad++, it shows as ?123456.

该列设置为TEXT.我无法控制此数据类型，因此无法更改它.

The column is set to TEXT. I have no control over this data type, so I can't change it.

如何修改我的 SELECT 查询以去除任何非数字内容.我怀疑 RegEx 是要走的路……或者，是否有 CAST 或 CONVERT 函数?

How can I modify my SELECT query to strip out anything non-numeric. I suspect RegEx is the way to go... alternatively, is there a CAST or CONVERT function?

推荐答案

您提到为此使用正则表达式.Access 的数据库引擎确实不直接支持正则表达式.但是，您似乎愿意在查询中使用 VBA 用户定义函数……而 UDF 可以使用正则表达式方法.与遍历输入字符串的每个字符并仅将您想保留在新输出字符串中的那些字符相比，这种方法应该简单、容易且执行速度更快.

You mentioned using a regular expression for this. It is true that Access' db engine doesn't support regular expressions directly. However, it seems you are willing to use a VBA user-defined function in your query ... and a UDF can use a regular expression approach. That approach should be simple, easy, and faster performing than iterating through each character of the input string and storing only those characters you want to keep in a new output string.

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

以下是立即窗口中该函数的示例，其中x"字符作为不可见字符的代理.(任何未包含在digits"字符类中的字符将被丢弃.)

Here is an example of that function in the Immediate window with "x" characters as proxies for your invisible characters. (Any characters not included in the "digits" character class will be discarded.)

? OnlyDigits("x1x23x")
123

如果这是您想要的输出，只需在查询中使用该函数即可.

If that is the output you want, just use the function in your query.

SELECT OnlyDigits(SomeCol) FROM SomeTable;

这篇关于去除 SELECT 中的非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！