Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Strip out non-numeric characters in SELECT

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

SELECT SomeCol FROM SomeTable

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.

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

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?

like image 983
Danny Beckett Avatar asked Sep 24 '12 11:09

Danny Beckett


People also ask

How do I remove non-numeric characters in SQL?

Using Regular Expression:select to_number(regexp_replace('Ph: +91 984-809-8540', '\D', '')) OUT_PUT from dual; In this statement '\D' would find all Non-digit characters and the will be replaced by null.

How do I remove non-numeric characters from a string?

In order to remove all non-numeric characters from a string, replace() function is used. replace() Function: This function searches a string for a specific value, or a RegExp, and returns a new string where the replacement is done.

How do I strip a character in SQL?

SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.

How do you delete an alphanumeric character in SQL?

You can change the NOT IN (...) expression to exclude specific characters, or change it to an IN (...) OR LIKE expression to retain only certain characters. SELECT ( SELECT SUBSTRING([YourString], N, 1) FROM dbo.


2 Answers

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

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;
like image 118
HansUp Avatar answered Oct 06 '22 13:10

HansUp


There is no RegEx in Access, at least not in SQL. If you venture to VBA, you might as well use a custom StripNonNumeric VBA function in the SQL statement.

e.g. 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
like image 37
RichardTheKiwi Avatar answered Oct 06 '22 13:10

RichardTheKiwi