Partial v. exact match 1

emozley · Mar 20, 2007

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

AlexCuse · Mar 20, 2007

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

Crowley16 · Mar 20, 2007

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!

AlexCuse · Mar 20, 2007

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

emozley · Mar 20, 2007

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

emozley · Mar 20, 2007

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...

PHV · Mar 20, 2007

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

AlexCuse · Mar 20, 2007

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

lespaul · Mar 20, 2007

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

emozley · Mar 21, 2007

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

AlexCuse · Mar 21, 2007

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

emozley · Mar 21, 2007

This is fantastic thank you very much!

AlexCuse · Mar 21, 2007

Glad it worked for you

Ignorance of certain subjects is a great part of wisdom

PHV · Mar 21, 2007

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

AlexCuse · Mar 21, 2007

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

groleau · Apr 25, 2007

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

http://www.Parkview.com/

groleau · Apr 25, 2007

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

http://www.Parkview.com/

groleau · Apr 25, 2007

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

http://www.Parkview.com/

AlexCuse · Apr 25, 2007

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

groleau · Apr 25, 2007

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

http://www.Parkview.com/

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!

Partial v. exact match 1

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

MIS

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

MIS

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor