Cut non-numeric characters in SELECT

Question

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

SELECT SomeCol FROM SomeTable 

The problem is that SomeCol appears to contain 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 displays as ?123456 .

The column is set to TEXT . I can not control this data type, so I can not change it.

How do I modify my SELECT query to cross out something non-numeric. I suspect RegEx is the way to go ... alternatively, is there a CAST or CONVERT ?

Answer 1

You mentioned using regex for this. It is true that the Access db engine does not support regular expressions directly. However, it looks like you are ready to use the custom VBA function in your request ... and UDF can use the regex approach. This approach should be simple, easy and faster than repeating each character of the input line and saving only those characters that you want to keep in a new output line.

 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 this function in the Immediate window with the characters "x" as a proxy for your invisible characters. (Any characters not included in the character class "digits" will be discarded.)

 ? OnlyDigits("x1x23x") 123 

If this is what you want, just use the function in your request.

 SELECT OnlyDigits(SomeCol) FROM SomeTable; 

Answer 2

Access lacks RegEx, at least not in SQL. If you decide on VBA, you can also use the StripNonNumeric VBA custom function in your SQL statement.

eg. 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 

Answer 3

You can do all this in a query by combining this question with your previous question , you will get:

 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; 

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 

Final function