Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Partial v. exact match 1

Status
Not open for further replies.

emozley

Technical User
Jan 14, 2003
769
GB
I have an access database that contains information about books - title, author publisher.

I am trying to make a search form that allows someone to choose whether they want to match the whole word or go for a partial match. A partial match is easy:

SELECT Author FROM Books WHERE Author LIKE '%Adam%'

Let's say this returns two records:

1. Douglas Adams
2. Adam Smith

How can I modify the SQL so that if the user specifies match whole word on the form and they search for 'Adam' it will ignore 'Douglas Adams' and only show 'Adam Smith'.

I am wary of putting spaces on either side of the search term because the name might appear at the start, middle or end.

eg SELECT Author FROM Books WHERE Author LIKE '% Adam %' wouldn't work because there is no space before 'Adam Smith'.

Is it possible to do something like

SELECT Author FROM Books WHERE " " & Author & " " LIKE '% Adam %'

or would this either not work at all or sometimes miss results?

Thanks very much!

Ed
 
You might look into using regular expressions for this, I have never used them in VBA but I imagine its' possible. I can't think of a way to do this with pure SQL.

If nobody replies in the next few minutes I will cobble together an example for you :)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
I'm not that sure of pattern matching in jet sql, but I believe you can do something like:

where blah like "*[_]Word[_]*"

you'll have to find out what goes in the []s since I believe _ mean any single character, where you want single space...

--------------------
Procrastinate Now!
 
Hey emozley, building on Crowley's idea, I think that something like this might work for you:

Code:
where name like 'Adam[ ]*' or name like '*[ ]Adam[ ]*' or name like '*[ ]Adam'

Its' not the neatest thing, but it seems to cover all possibilities. I will show you regex thing in a little bit, I just need to change it from vbScript.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Using the square brackets doesn't seem to work - is this specific to MS SQL? I am using Access.

So far

SELECT Author FROM Books WHERE " " & Author & " " LIKE '% Adam %'

Is working quite well however if the Author field was "Adam, Ben and Charles" it won't find it because of the comma after Adam.

In pseudo SQL I would like to do something like

SELECT Author FROM Books WHERE " " & Author minus punctuation & " " LIKE '% Adam %'

Regular expressions could be the way to go but can they be used with an Access Query?

Thanks very much
 
The only way I have so far to strip out certain punctuation is as follows:

SELECT Author, Title
FROM Catalogue
WHERE " " & Replace(Replace(Replace(Author,","," "),"."," "),";"," ") & " " Like '% Adam %';

Not sure if I am barking up completely the wrong tree with this approach...
 
You may try this:
WHERE ' ' & Author & ' ' LIKE '* Adam *'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
What I just posted for you was tested in access 2003.

I am pretty sure that % mean just one more character in access? (Or are you using ansi-92 syntax?)

Let me know if you would like to try regex, it will involve some coding on your part, but its' probably the best way to check the pattern because of its' ability to check for 'word borders'.



Ignorance of certain subjects is a great part of wisdom
 
mozley,
the problem comes from the way the LIKE operator works:

Like "AC*"
Finds all records that START with AC
Access
Account

Like "*AC"
Finds all records that END with AC
Lilac
Prozac

LIKE "*AC*"
Finds all records that CONTAIN AC
Access
Account
Back
Pact
Lilac
Prozac

Additionally, the Access Like operator is *, by using % that indicates that you are using another backend database (SQL Server, DB2, Paradox, etc.)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins
 
Hi,

I've got the ANSI 92 syntax box ticked for SQL server compatibility (in case I ever have to migrate).

I tried

where name like 'Adam[ ]*' or name like '*[ ]Adam[ ]*' or name like '*[ ]Adam'

with ansi 92 and without and if the name field contained Adam, Ben and Charles it wouldn't find it.

I wouldn't mind having a look at regexp but am not really sure where to begin! Would this be able to pattern match and filter out punctuation?

Thanks very much
 
Ok emozley, I think that despite the performance hit, if you are worried about punctuation as well regex is going to be the way to go (for your exact match only), because you will drive yourself nuts thinking of all the different things people can enter!

So here goes (must have vbScript installed on the machine)

First, paste this into a standard code module:
Code:
Option Compare Database

Function RegXChk(strCol, strSearch)
    
       Dim Reg As Object
       Dim tst As Boolean
       
       'create RegEx object (VBScript must be installed)
       Set Reg = CreateObject("VBScript.RegExp")
       With Reg
               .Pattern = CreatePattern(strSearch)
               .IgnoreCase = True
       End With
       
       tst = Reg.Test(strCol)
       
       RegXChk = tst
       
       Set Reg = Nothing
End Function

Function CreatePattern(strSearch)
    'assign your search pattern.  For exact match
    'use "\b(Adam)\b"  \b = word boundary
    
    strSearch = "\b(" & strSearch & ")\b"

    CreatePattern = strSearch

End Function

You then call it from a query, like this:
Code:
SELECT name
FROM name
WHERE RegXChk(name, 'Adam') = True;

Of course, where I have typed 'Adam', you would be passing a parameter from your form.

Here is the data I tested this on:
Code:
Douglas Adams
Steve Adam Smith
Adam Stevenson
John Adam
Adam, Steven
John, Adam, Steven
Adam!
Adamson, Adams
Adams, Adam's
Adams
Adam's
Adam?
David Adam
David Adamson
adam
adams
David, Adam
Adam, David

And the results:
Code:
Steve Adam Smith
Adam Stevenson
John Adam
Adam, Steven
John, Adam, Steven
Adam!
Adams, Adam's
Adam's
Adam?
David Adam
adam
David, Adam
Adam, David

Is that about what you were after?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Glad it worked for you :)

Ignorance of certain subjects is a great part of wisdom
 
ANSI 92 syntax
WHERE ' ' || Author || ' ' LIKE '% Adam %'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PH, I don't think that will work with punctuation.

And it also appears that Access' implementation of the ANSI-92 standard does not go so far as to include the vertical bars (at least US version)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
The regular expression is much more flexible/versatile,
but this particular case can be done with Like.

Look at the earlier post with three options *xx xx* *xx*
and consider:
Code:
"[!A-Za-z]" = anything that is not a letter
"*"     = anything at all, including nothing
So you can put those two together instead of just "*"

In other words, "Adams" before "[!A-Za-z]*" or after "*[!A-Za-z]" or both.

--
Wes Groleau
 
It occurs to me I should have added a correspondence tip for
folks familiar with Like or regexp but not both.
Code:
Like (Jet)           RegExp            Like (ANSI)
==========           ======            ==========
 [!        corr. to    [^
 *          "     "    .*    corr. to      %
 ?          "     "    .     corr. to      _
 "X         "     "    "^X   corr. to      "X
 X"         "     "    X$"   corr. to      X"
 #          "     "   [0-9]

--
Wes Groleau
 
Calling VBA functions in Jet SQL queries probably should be a FAQ but obviously I am not the one to write it, as mine doesn't work. In VBA, I have
Code:
Function SVC_01_01(SVC01 As String) As String
    ' various computations skipped
    if ........ then
        SVC_01_01 = SVC_01
    else
        ' various computations skipped
        SVC_01_01 = Parts(0)
    end if
End Function ' SVC_01_01
In the query's SQL view,
Code:
SELECT SVC_01_01(Service) AS Codeset
FROM Data_For_Analysis;
Result: 800K records of "#Error"

--
Wes Groleau
 
Can you show your whole function?

I don't get what exactly you are trying to do there.

Ignorance of certain subjects is a great part of wisdom
 
The Jet Engine never even called the function
(at least it never stopped on the breakpoint I put in it).
But for what it's worth:
Code:
Public Function SVC_01_01(SVC01 As String) As String

    Dim Delim   As String
    Dim Parts() As String
    
    If Len(SVC01) < 3 Then
    
        SVC_01_01 = SVC_01
    
    Else
    
        Delim = Mid(SVC01, 3, 1)
        Parts = Split(SVC01, Delim)
        SVC_01_01 = Parts(LBound(Parts))
    
    End If

End Function ' SVC_01_01
I found the answer, however. A few of the fields are null.
Apparently Jet says "I can't call the function on some of the
rows, so I refuse to call it on any of them."

Solution: First do an update replacing Null with ""
Or: Do an iif(x is null,"", function(x))

--
Wes Groleau
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top