Solved

find text pattern in a string

Posted on 2010-09-10
24
329 Views
Last Modified: 2012-05-10
I have a string:

 106 1.6i 16V

I would like a function that can identify if the string contains 1.6i so: number period number followed by the i.

it can return true or false

of course the numbers will change ie 1.8, 1.4 etc

regards in advance

0
Comment
Question by:PeterBaileyUk
  • 11
  • 9
  • 4
24 Comments
 
LVL 75
ID: 33652282
" number period number followed by the i."
Well, that's not what you are showing

1.6i

is Number Period Number i

??

mx
0
 

Author Comment

by:PeterBaileyUk
ID: 33652371
looks the same to me but yes: Number Period Number i, tell me what you interpreted so that I wont make the same mistake again! ;)

0
 
LVL 75
ID: 33652388
wow lol ... it IS the same. Man, I kept looking at that and seeing something else. It MUST be late ...

So ... would there ever be more than one Period or more than one i ?  Because if not, that seems all you would have to do is detect the period or the i  ... ?  Especially if the format is always
number period number i  

?

mx
0
 
LVL 75
ID: 33652432
Well, I need to zzzz ... but in case what I asked is the case ... then you can detect the i or .  like so, using the InStr() function

If InStr(1, YourString ,".") > 0 Then
    ' Do what evern
End If

Or in a query like so:
SELECT Table1.*
FROM Table1
WHERE (((InStr(1,[FIELD1],".")>0)=True));

mx
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 33652437
One option is to add some code as below to a normal code module and then to extract the data use:

msgbox getDatabyRegEx(" 106 1.6i 16V", "\b[0-9].[0-9]i\b")(0)

The (0) returns the first found

To see all then for example

Sub testall()
Dim arr() As String
arr = getDatabyRegEx(" 106 1.6i 16V plus 1.8i and a 1.22i", "\b[0-9].[0-9]i\b")
    MsgBox UBound(arr) + 1 & " entries found ... LAst one is " & arr(UBound(arr))
End Sub

will ignore 1.22i because you said number which I interpreted as exactly 1 but can modify.

Chris
Function getDatabyRegEx(strFindin As String, strPattern As String, Optional strReplacement As String = "", Optional bolGlobalReplace As Boolean = True, Optional bolMatchCase As Boolean = False) As Variant

Dim colmatch As Object

Dim itm As Variant

Dim retArray() As String

Dim intBounds As Integer



    intBounds = -1

    If valDatabyRegEx(strFindin, strPattern, bolMatchCase) Then

        With CreateObject("vbscript.regexp")

            .IgnoreCase = Not bolMatchCase

            .Global = bolGlobalReplace

            .Pattern = strPattern

            Set colmatch = .Execute(strFindin)

            If bolGlobalReplace Then

                For Each itm In colmatch

                    intBounds = intBounds + 1

                    ReDim Preserve retArray(0 To intBounds)

                    retArray(intBounds) = itm

                Next

            Else

                ReDim retArray(0)

                retArray(0) = colmatch(0)

            End If

        End With

        getDatabyRegEx = retArray

    End If

    

End Function

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652443
For what it's worth

Replacing:
"\b[0-9].[0-9]i\b"
in any of the above with
\b[0-9]+.[0-9]+i\b

will support for example 22.33.i

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652444

will support for example 22.33i

for example ... doh!
0
 

Author Comment

by:PeterBaileyUk
ID: 33652466
what could change is 1.3si etc the nominal cc ie 1.2, 2.3 etc will change the i tagged on the end represents fule delivery method. I have a scoring system that does various tests but in one example two vehicles with the same engine get the same score the only difference was the fuel delivery. An expert gave me advice before on a function to get the nom cc from a string ie the 1.2 etc and I suspect that was chris as the form is similar to your suggestion.

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652471
To try and capture the variations ... we do need to know the variations.  for example we could allow any alpha before the i or only specific ones.

The more info you can provide (on permutations) the more we can do to help with an appropriate solution.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652474
"\b[0-9]+.[0-9]+[A-Z]{0,2}I\b"

Will allow 1.2i as well as 2.3eSi

Chris
0
 

Author Comment

by:PeterBaileyUk
ID: 33652479
I gouped the textual descriptions but there were too many so if I start off little and see where it leads with the test itself, rather than try and show every permutation. just off to experiment.
0
 

Author Comment

by:PeterBaileyUk
ID: 33652505
from the immediate window i did:
?getDatabyRegEx("106 1.6i 16V","\b[0-9]+.[0-9]+[A-Z]{0,2}I\b")(0)

but it says:
valDatabyRegEx not defined

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652510
Did you add the supplied sub to a normal code module?

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652516
Sorry, RTFQ!, which I have now done!

Chris
Function valDatabyRegEx(strFindin As String, strPattern As String, Optional bolMatchCase As Boolean = False) As Boolean

    

    With CreateObject("vbscript.regexp")

        .IgnoreCase = Not bolMatchCase

        .Pattern = strPattern

        valDatabyRegEx = .test(strFindin) = True

    End With

    

End Function

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652530
Reading the original question ... you actually wanted to know if a matching string is there whereas the suggested code returns THE value found.

Therefore to simply return true/false reflecting found then use:

Noting a bug ... heaven forbid use:

?getDatabyRegEx("106 1.6]3i 16V","\b[0-9]+\.[0-9]+[A-Z]{0,2}I\b")

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652537
Use of getDatabyRegEx if the string is not found returns an error via the command line so that's fixed as below ... hopefully

i.e.
msgbox getDatabyRegEx(" 106 1.6;i 16V", "\b[0-9]\.[0-9]i\b")(0)
was returning an error but with the below modification doesn't any longer.

Chris
Function getDatabyRegEx(strFindin As String, strPattern As String, Optional strReplacement As String = "", Optional bolGlobalReplace As Boolean = True, Optional bolMatchCase As Boolean = False) As Variant

Dim colmatch As Object

Dim itm As Variant

Dim retArray() As String

Dim intBounds As Integer



    intBounds = -1

    If valDatabyRegEx(strFindin, strPattern, bolMatchCase) Then

        With CreateObject("vbscript.regexp")

            .IgnoreCase = Not bolMatchCase

            .Global = bolGlobalReplace

            .Pattern = strPattern

            Set colmatch = .Execute(strFindin)

            If bolGlobalReplace Then

                For Each itm In colmatch

                    intBounds = intBounds + 1

                    ReDim Preserve retArray(0 To intBounds)

                    retArray(intBounds) = itm

                Next

            Else

                ReDim retArray(0)

                retArray(0) = colmatch(0)

            End If

        End With

        getDatabyRegEx = retArray

    Else

        getDatabyRegEx = Array("")

    End If

    

End Function

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 33652567
i will be using vba for this but was just testing
0
 

Author Comment

by:PeterBaileyUk
ID: 33652574
interesting i still get sub or function not defined, the code is in module 1 and calling :
?getDatabyRegEx("106 1.6i 16V","\b[0-9]+.[0-9]+[A-Z]{0,2}I\b")(0)
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33652617
You have both subs getDatabyRegEx  & valDatabyRegEx in module 1?

Chris
0
 

Author Comment

by:PeterBaileyUk
ID: 33653668
i have regexpfind and regexpreplace in module one.
0
 

Author Comment

by:PeterBaileyUk
ID: 33653670
i missed it now its installed
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 33653694
excellent thank you
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33653908
Glad it helped

Chris
0
 
LVL 75
ID: 33653961
I see this Q escalated a bit and there was more than meets the eye  :-)

mx
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now