Search Numeric Values

11 Jul 2005 9:52 AM

amit cumar

I have a large growing database and from a paticular field i need to
find out the numeric values out of the string field of length 200 to
400

Ex.                  col 1
        row 1        werwer 32,3 ererw
        row 2        4/5 ksdj sdjfk,44 222
        -
        -
        row n        4-5 ,jhdguiusd,333,4

I need from
        row 1 33 and 3
        row 2 4,5,44 and 222
        row n 4,5, 333,4

Can any one help me out

*** Sent via Developersdex http://www.developersdex.com ***

11 Jul 2005 2:41 PM

Tim Baur

amit cumar <amitcu***@yahoo.co.in> wrote in news:eoTdP5fhFHA.3164
@TK2MSFTNGP15.phx.gbl:

Show quoteHide quote

>
> I have a large growing database and from a paticular field i need to
> find out the numeric values out of the string field of length 200 to
> 400
>
> Ex.                  col 1
>         row 1        werwer 32,3 ererw
>         row 2        4/5 ksdj sdjfk,44 222
>         -
>         -
>         row n        4-5 ,jhdguiusd,333,4
>
> I need from
>         row 1 33 and 3
>         row 2 4,5,44 and 222
>         row n 4,5, 333,4
>
> Can any one help me out
>
> *** Sent via Developersdex http://www.developersdex.com ***

This kind of thing is asked pretty often. You might be able to find a
better algorithm if you Google the groups.

Here's one approach.

Replace everything that's not a number with a space.
Replace all double spaces with single spaces
Call split on the resulting string.

* * * * * * * *

Public Function ParseNumbers(sVal As String) As Variant
    Dim iPos As Integer
    Dim sChar As String
    Dim sRetVal As String

    ' Catch all numbers and replace all other characters _
      with spaces
    For iPos = 1 To Len(sVal)
        sChar = Mid(sVal, iPos, 1)
        If sChar > "0" And sChar < "9" Then
            sRetVal = sRetVal & sChar
        Else
            sRetVal = sRetVal & " "
        End If
    Next iPos

    ' Trim the first and last delimeters
    sRetVal = Trim(sRetVal)

    ' Remove double delimeters
    Do Until InStr(1, sRetVal, " ") = 0
        sRetVal = Replace(sRetVal, " ", " ")
    Loop

    ParseNumbers = Split(sRetVal, " ")

End Function

* * * * * * * *

11 Jul 2005 4:30 PM

Rick Rothstein

Show quote Hide quote

> > I have a large growing database and from a paticular field i need to
> > find out the numeric values out of the string field of length 200
to
> > 400
> >
> > Ex.                  col 1
> >         row 1        werwer 32,3 ererw
> >         row 2        4/5 ksdj sdjfk,44 222
> >         -
> >         -
> >         row n        4-5 ,jhdguiusd,333,4
> >
> > I need from
> >         row 1 33 and 3
> >         row 2 4,5,44 and 222
> >         row n 4,5, 333,4
> >
> > Can any one help me out
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>
> This kind of thing is asked pretty often. You might be able to find a
> better algorithm if you Google the groups.
>
> Here's one approach.
>
> Replace everything that's not a number with a space.
> Replace all double spaces with single spaces
> Call split on the resulting string.
>
> * * * * * * * *
>
> Public Function ParseNumbers(sVal As String) As Variant
>     Dim iPos As Integer
>     Dim sChar As String
>     Dim sRetVal As String
>
>     ' Catch all numbers and replace all other characters _
>       with spaces
>     For iPos = 1 To Len(sVal)
>         sChar = Mid(sVal, iPos, 1)
>         If sChar > "0" And sChar < "9" Then
>             sRetVal = sRetVal & sChar
>         Else
>             sRetVal = sRetVal & " "
>         End If
>     Next iPos
>
>     ' Trim the first and last delimeters
>     sRetVal = Trim(sRetVal)
>
>     ' Remove double delimeters
>     Do Until InStr(1, sRetVal, " ") = 0
>         sRetVal = Replace(sRetVal, " ", " ")
>     Loop
>
>     ParseNumbers = Split(sRetVal, " ")
>
> End Function
>
> * * * * * * * *

The only possible "fly in the ointment" for your routine is if the
numbers can be floating point and the text can have sentences that
contain the decimal separator character (I'm thinking of EITHER the
"dot" character being a decimal point and period at end of sentence OR
the comma as the decimal "point" and a phrase separator in a sentence).
Of course, the OP needs to tell us if this can be the case.

Rick

11 Jul 2005 4:38 PM

Tim Baur

"Rick Rothstein" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in news:#

89FnXjhFHA.3***@TK2MSFTNGP12.phx.gbl:

>
> The only possible "fly in the ointment" for your routine is if the
> numbers can be floating point and the text can have sentences that
> contain the decimal separator character (I'm thinking of EITHER the
> "dot" character being a decimal point and period at end of sentence OR
> the comma as the decimal "point" and a phrase separator in a
sentence).
> Of course, the OP needs to tell us if this can be the case.
>
> Rick
>
>

Thanks for pointing that out, Rick. I thought of it when doing the
post, but forgot to mention it.

I'm thinking a decimal notation parameter of some sort on the function
would be in order. The caller could specify the point character or
leave it blank if you integer values are desired. This character would
be included in the comparison.

12 Jul 2005 11:56 AM

Tony Proctor

Here's a slightly quicker version of your method Tim that avoids too much
memory re-allocation for strings:

Public Function ParseNumbers(ByVal sVal As String) As String()
Dim iPos As Integer
Dim sChar As String
Const SP As String = " "
Const SP2 As String = SP & SP

    ' Catch all numbers and replace all other characters with spaces
    For iPos = 1 To Len(sVal)
        sChar = Mid$(sVal, iPos, 1)
        If sChar < "0" Or sChar > "9" Then
            Mid$(sVal, iPos, 1) = SP
        End If
    Next iPos

    ' Trim leading/trailing delimeters
    sVal = Trim(sVal)

    ' Remove double delimeters
    Do Until InStr(1, sVal, SP2) = 0
        sVal = Replace(sVal, SP2, SP)
    Loop

    ParseNumbers = Split(sVal, SP)
End Function

Also, the OP wanted 33 and 3 from row 1, although the correct answer is of
course 32 and 3 :-)

            Tony Proctor

Show quoteHide quote

"Tim Baur" <trbo20DIS***@ARDyahoo.com> wrote in message
news:Xns96906CCD7620Btrbo20DISREGARDyahoo@207.46.248.16...
> amit cumar <amitcu***@yahoo.co.in> wrote in news:eoTdP5fhFHA.3164
> @TK2MSFTNGP15.phx.gbl:
>
> >
> > I have a large growing database and from a paticular field i need to
> > find out the numeric values out of the string field of length 200 to
> > 400
> >
> > Ex.                  col 1
> >         row 1        werwer 32,3 ererw
> >         row 2        4/5 ksdj sdjfk,44 222
> >         -
> >         -
> >         row n        4-5 ,jhdguiusd,333,4
> >
> > I need from
> >         row 1 33 and 3
> >         row 2 4,5,44 and 222
> >         row n 4,5, 333,4
> >
> > Can any one help me out
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>
> This kind of thing is asked pretty often. You might be able to find a
> better algorithm if you Google the groups.
>
> Here's one approach.
>
> Replace everything that's not a number with a space.
> Replace all double spaces with single spaces
> Call split on the resulting string.
>
> * * * * * * * *
>
> Public Function ParseNumbers(sVal As String) As Variant
>     Dim iPos As Integer
>     Dim sChar As String
>     Dim sRetVal As String
>
>     ' Catch all numbers and replace all other characters _
>       with spaces
>     For iPos = 1 To Len(sVal)
>         sChar = Mid(sVal, iPos, 1)
>         If sChar > "0" And sChar < "9" Then
>             sRetVal = sRetVal & sChar
>         Else
>             sRetVal = sRetVal & " "
>         End If
>     Next iPos
>
>     ' Trim the first and last delimeters
>     sRetVal = Trim(sRetVal)
>
>     ' Remove double delimeters
>     Do Until InStr(1, sRetVal, " ") = 0
>         sRetVal = Replace(sRetVal, " ", " ")
>     Loop
>
>     ParseNumbers = Split(sRetVal, " ")
>
> End Function
>
> * * * * * * * *

12 Jul 2005 1:01 PM

Tim Baur

"Tony Proctor" <tony_proctor@aimtechnology_NoMoreSPAM_.com> wrote in
news:ee979ithFHA.1148@TK2MSFTNGP12.phx.gbl:

> Mid$(sVal, iPos, 1) = SP

Thanks, Tony. That is cleaner. No matter how long I do this, I don't
think I'll never get it into my head that Mid can take an assignment.