need "exact match within string" in search algo

[WozFromOz]

Using asp to query Access 97 db on IIS5

I need to find exact matches for search terms within a string. EG "cat" will find cat but not catastrophe.

I cannot use "=" as that would match the whole string and I only want to find the exact match within the string.

I am trying "like '%[!a-z][search term][!a-z]%'" which is pretty clumsy and also doesn't pick up the search term if it is at the beginning of a sentence.

Can anyone help me with the syntax here? I have racked my brains coming up with all sorts of convoluted solutions that all end up having a flaw. I am sure there must be some operator within SQL as there is in FMP but I cannot find it.

Thanks all

Onya
WozfromOz

 

[ghubbell]

Hi Onya,
Well...in Access if you were to query for Cat and want to get "Catagory" or "Catastrophe", you're criteria would be:

Like "cat*"

and if grabbing data off of an Access form:

Like [Forms]![NameOfTheForm]![NameOfTheField] & "*"

ASP to query Access 97 db on IIS5...not a clue... but perhaps these ideas will head you in a the right direction.
Hope it helps!



Gord
ghubbell@total.net

 

[ghubbell]

Geeze did I read that backwards. Pitch the like and "*" for an even better idea. (nap time gord.) Gord
ghubbell@total.net

 

[WozFromOz]

thank Gord, thanks for the relpy,

maybe I didn't explain my problem well enough.

Like *at* gets "at" "cat" "catastrophe" "attention" etc

Like at* gets "attention" only

how do I get "at" from within a string? =at matches the whole string. I want to get an exact match on a substring within a string. Follow?

WozFromOz

 

[ghubbell]

Hi Onya,
If you have Access you probably have the Northwinds sample database. If you could find it then open it and open a new query, no selected table, in design view. Click on the SQL button top left, and paste this in:

SELECT Customers.CompanyName, IIf(InStr([CompanyName],"at&quot=0,"at&quot AS HopeThisIsWhatYouAreAfter
FROM Customers
WHERE (((IIf(InStr([CompanyName],"at&quot=0,"at&quot) Is Not Null));

Then flip the query to regular view. This is the InStr function within "Immediate If" (IIF) to give you a result that is...hopefully what you're after! I think from there you can modify the idea to suit your needs. Gord
ghubbell@total.net

 

[KavJack]

A week ago I had Office 97 running under Windows 95. I had to upgrade to Windows 98. Now my SQL containing function LEFT and MID won't work. InStr is working though.
What do I have to do to get my SQL working.
This SQL is suposed to find all teams named United in a database and replace it with a U;
i.e. Abingson United becomes Abingon U
UPDATE [CP1996P]
SET [CP1996P].AT =
Left([CP1996P].[AT],InStr([CP1996P].[AT]," United&quot) & "U"
WHERE (((InStr([CP1996P].[AT]," United&quot)>"0&quot) AND YR>1999;
I understand that there were some other people find problems running Office 97 in WIndows 98 but I could not find any resolution.

 

[ghubbell]

Gremlins...
KavJack, Check in VB Tools-References. You need MS DAO 3.6 Object Library and possibly VB for apps Extensibility 5.3 . The priority is really important so try to compile the code, then if unsucessful, move them around till it does. On the computer in front of me (2000) I have VB for Apps, MS Access 9.0 (97=8), OLE Automation, DAO 3.6 (97=3.5x) and ActiveX D.O. 2.1 in that order. No "Missing" stuff either. If this fails, make a new empty Db and import all your old stuff (don't forget tool/menu bars) in to the new, and try again.
Gord
ghubbell@total.net

 

[WozFromOz]

Thanks Gord, however that routine finds entries that do not contain the search term. I am still looking for an answer.

So far I am using

SELECT * from Table WHERE (' ' & Field) LIKE %[!#,?]TERM[!#,?]%

with some success. Any imrpovements anyone?

Onya (Aussie Greeting)
WozFromOz (Name) HeHe!

 

[ghubbell]

I've got it now!
Sorry mate, une petite problemé avec la langue ici! (a small problem with the language here)....

I really don't know ASP but you must have an option other than Like? You want a specific result in your query, so must have to be able to ask a specific question some way.

I'll leave you in the capable hands of others...

Onya WozFromOz!
Gord
ghubbell@total.net

 

[MichaelRed]

SELECT tblGrade.StuName, tblGrade.Asgn01,
IIf(InStr([StuName],&quot;Carr&quot<>0,True,False) AS MYGuy
FROM tblGrade
WHERE (((IIf(InStr([StuName],&quot;Carr&quot<>0,True,False))=True));


Standard select stuff from table/recordsource
SELECT tblGrade.StuName, tblGrade.Asgn01,

Create a field which is TRUE where the string exists
IIf(InStr([StuName],&quot;Carr&quot<>0,True,False) AS MYGuy

Standard Statement of Recordsource
FROM tblGrade

Conditional to select the desired records
WHERE (((IIf(InStr([StuName],&quot;Carr&quot<>0,True,False))=True));

Gord really has it - he just needs some Twinkies and a Coke!!!




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

And more than one each! Gord
ghubbell@total.net

 

[WozFromOz]

Nup! Still won't work but thanks anyway Michael. You syntax still only picks up &quot;carr&quot; as the whole field as there are no delimiters on either side.

Onya
WozFromOz

 

[MichaelRed]

You are aparently not implementing this properly. I used it on a small recordset to retrieve the record for &quot;Carroll&quot; and it does select the record. I do not know how you went about it but however you did it, it is not 'propper' (e.g. following the example) because it does work.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

As did I with my sample Michael. Woz seems to be working with~in ASP. Gord
ghubbell@total.net

 

[WozFromOz]

Gord & Micahel, thanks very much for you help but I think you are still missing my request. My fault for not explaing enough.

I tried both your suggestions in access and asp (pretty much the same thing) and they do work but they don't do what I want. They both find the occurence of a substring within the string regardless of the surroundings. I only want to find the occurence if the substring is a complete word within the string.

So, if I am searching for &quot;cat&quot; I only want a result if &quot;cat&quot; is present as a complete word and not if the three letters cat are part of a longer word.

So, search for an exact match on &quot;cat: within the string finds &quot;It was a cat on the roof&quot; but not &quot;It was a catastropy at the park&quot;. Gotit?

I am sorry but it seems to be a really difficult one to explain. Digging through several fora I have seen quite a few people ask the same question with no luck.

Thanks
Onya
WozFromOz

 

[ghubbell]

Hi Woz,
If you have the Northwinds sample database (you should if you have Access) find it and open a new query. No table. select SQL view and paste this:

SELECT Customers.CompanyName
FROM Customers
WHERE (((Customers.CompanyName)=&quot;Mère Paillarde&quot);

and flip to datasheetview. 1 criteria, 1 result exact match only.

Sur Toi (Onya)
Gord

Gord
ghubbell@total.net

 

[link9]

If I may interject --

Could you not search for the string you are looking for and just place a space on either side of the string??

Would this not ensure that the string existed as a complete word??

I might be completely off base, but it seems like that might work.

 

[MichaelRed]

So, there are three cases?

In the following, &quot;space&quot; actually refers to any NON-PRINTING character (an actual space), punctuation, or 'high ascii). Or PERHAPS anthing not in the range of &quot;a-&quot;z&quot; or &quot;A-Z&quot;.

1. No leading space & &quot;Word&quot; & trailing space(Start of Field)

2. Leading space, & &quot;Word&quot; And trailing space(embeded in the field)

3. Leading space& &quot;Word&quot; & no trailing space(end of field)

If this is &quot;correct&quot;, then it IS more difficult - but still emminently doable. I would implement part of this in a module called from the query:

SELECT tblGrade.StuName, tblGrade.Asgn01, basCheckForFullString(&quot;Carroll&quot;,[StuName]) AS MYGuy
FROM tblGrade
WHERE (((basCheckForFullString(&quot;Carroll&quot;,[StuName]))=True));

Public Function basCheckForFullString(StrIn As String, FieldIn As String) As Boolean

    'Check for the existance of StrIn within FieldIn as a WHOLE word

    Dim SubStrPos As Integer
    Dim FieldLen As Integer
    Dim SubStrLen As Integer
    Dim EndChr(1) As String * 1             'Lead/Trail Chrs

    SubStrLen = Len(StrIn)
    FieldLen = Len(FieldIn)

    SubStrPos = 1               'Init Start Pos for InStr

    'Just check if it exists
    SubStrPos = InStr(SubStrPos, FieldIn, StrIn)

    'Trivial Cases: Not Present.
    If (SubStrPos = 0) Then                 'Pos = 0 ==> Not here
        basCheckForFullString = False       'Redundant/ more to SHOW
        Exit Function                       'Go Back
    End If

    
    'We only get here if StrIn is part of FieldIn
    'Trivial Cases: Whole Field
    If (SubStrLen = FieldLen) Then          'The SubStr IS the Field
        basCheckForFullString = True
        Exit Function
    End If

    'At least POSSIBLE complexity
    Do While SubStrPos <> 0     'Loop for all occurances of StrIn

        'Now, we know it is there - SOMEWHERE
        If (SubStrPos = 1) Then                 'SubStr at start
            'Get char following (If any)
            EndChr(1) = Mid(FieldIn, SubStrPos + SubStrLen)
            If (UCase(EndChr(1)) <= &quot;A&quot; Or UCase(EndChr(1)) >= &quot;Z&quot;) Then
                'It exists as a &quot;Whole Word&quot;, so just say so
                basCheckForFullString = True
                Exit Function
             Else
                'BUT, the next char is 'normal' so it's NOT a match
                GoTo NxtPos
            End If
        End If

        If (SubStrPos + SubStrLen - 1 = FieldLen) Then   'SubStr at End
            'Get char Preceeding (If any)
            EndChr(0) = Mid(FieldIn, SubStrPos - 1)
            If (UCase(EndChr(0)) <= &quot;A&quot; Or UCase(EndChr(0)) >= &quot;Z&quot;) Then
                'It exists as a &quot;Whole Word&quot;, so just say so
                basCheckForFullString = True
                Exit Function
             Else
                'BUT, the next char is 'normal' so it's NOT a match
                GoTo NxtPos
            End If
        End If

        'Here, only if StrIn is 'buried' in FieldIn
        EndChr(0) = Mid(FieldIn, SubStrPos - 1)
        EndChr(1) = Mid(FieldIn, SubStrPos + SubStrLen)
        If ((UCase(EndChr(0)) <= &quot;A&quot; Or UCase(EndChr(0)) >= &quot;Z&quot;) And _
            (UCase(EndChr(1)) <= &quot;A&quot; Or UCase(EndChr(1)) >= &quot;Z&quot;)) Then
           basCheckForFullString = True
           Exit Function
        End If

NxtPos:
        'See if there is another instance.
        SubStrPos = InStr(SubStrPos + 1, FieldIn, StrIn)
    Loop

End Function

So, try this one - IF my assumptions are Correct. NOTE, this is NOT case sensitive.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

link9,

not off base - but incomplete. Excludes instances where the 'string' is the first or last &quot;word&quot; (substring) in the field and where the &quot;word&quot; is preceeded or followed by punctuation or other non-printing characters.

Actually, the soloution I just posted is probably not complete, as I did not check for numerals, So searching for &quot;CAT&quot; will find a match for &quot;cat5&quot;. Oh, well it is getting closer - and more complicated.

Backin a few with this wrinkle!


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

O.K. I think I've covered this territority. Some poor soul needs to check some additional / other / more cases? BUt it appears to work on my testing.

Public Function basCheckForFullString(StrIn As String, FieldIn As String) As Boolean

    'Check for the existance of StrIn within FieldIn as a WHOLE word

    Dim SubStrPos As Integer
    Dim FieldLen As Integer
    Dim SubStrLen As Integer
    Dim EndChr(1) As String * 1             'Lead/Trail Chrs
    Dim OkFlg As Boolean

    SubStrLen = Len(StrIn)
    FieldLen = Len(FieldIn)

    SubStrPos = 1               'Init Start Pos for InStr

    'Just check if it exists
    SubStrPos = InStr(SubStrPos, FieldIn, StrIn)

    'Trivial Cases: Not Present.
    If (SubStrPos = 0) Then                 'Pos = 0 ==> Not here
        basCheckForFullString = False       'Redundant/ more to SHOW
        Exit Function                       'Go Back
    End If

    
    'We only get here if StrIn is part of FieldIn
    'Trivial Cases: Whole Field
    If (SubStrLen = FieldLen) Then          'The SubStr IS the Field
        basCheckForFullString = True
        Exit Function
    End If

    'At least POSSIBLE complexity
    Do While SubStrPos <> 0     'Loop for all occurances of StrIn

        'Make sure the ends are not leftovers
        EndChr(0) = &quot;&quot;
        EndChr(1) = &quot;&quot;

        'Now, we know it is there - SOMEWHERE
        If (SubStrPos = 1) Then                 'SubStr at start
            'Get char following (If any)
            EndChr(1) = Mid(FieldIn, SubStrPos + SubStrLen)
            GoSub ChkEndChr
            If (OkFlg) Then
                'It exists as a &quot;Whole Word&quot;, so just say so
                basCheckForFullString = True
                Exit Function
             Else
                'BUT, the next char is 'normal' so it's NOT a match
                GoTo NxtPos
            End If
        End If

        If (SubStrPos + SubStrLen - 1 = FieldLen) Then   'SubStr at End
            'Get char Preceeding (If any)
            EndChr(0) = Mid(FieldIn, SubStrPos - 1)
            GoSub ChkEndChr
            If (OkFlg) Then
                basCheckForFullString = True
                Exit Function
             Else
                'BUT, the next char is 'normal' so it's NOT a match
                GoTo NxtPos
            End If
        End If

        'Here, only if StrIn is 'buried' in FieldIn
        EndChr(0) = Mid(FieldIn, SubStrPos - 1)
        EndChr(1) = Mid(FieldIn, SubStrPos + SubStrLen)
        GoSub ChkEndChr
        If (OkFlg) Then
           basCheckForFullString = True
           Exit Function
         Else
           'BUT, the next char is 'normal' so it's NOT a match
           GoTo NxtPos
        End If

NxtPos:
        'See if there is another instance.
        SubStrPos = InStr(SubStrPos + 1, FieldIn, StrIn)
    Loop

    GoTo NormExit

ChkEndChr:
    OkFlg = True    'Assume the BEST

    For Idx = 0 To UBound(EndChr)
        Select Case UCase(EndChr(Idx))
            Case &quot;A&quot; To &quot;Z&quot;
                OkFlg = False
                Return
            Case &quot;0&quot; To &quot;9&quot;
                OkFlg = False
                Return
        End Select
    Next Idx

    Return          'Must Be O.K?

NormExit:
End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over