Stripping everything but numbers in Access



  • Hi there. I have fields in access with data that looks like this:

     

    ECS-9986044 

    A101132

    PSA #3411     

    122-1771A  

    P.O. 411894-G 

     

    I want to strip everything out that isn't a number, so I just have

    9986044

    101132

    3411

    1221771

    411894

     

    Any ideas? 

     

    Edit: By the way, I'm a long timer lurker, first time poster. Sorry that my first post is a silly question/demand! 



  • Can you not make a string buffer and loop through your string(s) putting any numeric character into the buffer?



  • I can't tell you how to strip them out in Access, but I just want to recommend that you may want to divide that 122-1771A field into two numbers in the end, 122, and 1771. You may have to sort by the first number or the second number and it could come back to bite you if they are concatenated.



  • Strip them using what? SQL or VB?



  • In access 2007 you could do use a custom function (in a module) to do the conversion, then call it from either SQL or VB.  It is NOT going to be the fastest thing in the world, but it should do what you want...

    Here is an example function:  (Note that this does not support periods anywhere in the string)

    Module:

    Public Function ParseDbl(inp As String) As Double
        Dim i As Integer
        Dim result As String
        Dim s As String
       
        For i = 1 To Len(inp) Step 1
            s = Mid(inp, i, 1)
            If IsNumeric(s) Then
                result = result & s
            End If
        Next
       
        ParseDbl = CDbl(result)
    End Function

    SQL:

    SELECT ParseDbl(Field1) AS Expr1
    FROM Table1;


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.