Search for exact Phrase

[katherinep]

Hi,

I have a search form that allows the user to search a field in a table called 'description'. The only thing is if I enter a keyword for example 'CAN' it comes up with not only all the records with the word CAN in it but also ones with words such as 'scan'. I want it to use * so it can find the keyword hidden amongst all the other text but I want to to search for the exact match and not pick up records where the keyword is part of other words.

Has anyone got any ideas if this is possible and how to do it?

Thankyou,

Katherine

 

[KenReay]

Hi

If it is a distinct word then maybe "* Myword *", but that wotkd not allow for it being at end of sentance eg Myword., not sure if that is relevant

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GHolden]

Hi

Check out the LIKE statement

eg.

IF varible LIKE '*up*'

will be true for anything with 'up' in it

ie pup up sup

where as

IF variable LIKE '* up *' (spaces in the like)

will only match the whole word up.

Hope this helps



There are two ways to write error-free programs; only the third one works.

 

[katherinep]

Hi,

Thanks for the help. I have just tried putting in

Like '* CAN *'

and it comes up with no results

if however if put in

Like '*CAN *'

It doesn't show the records with 'Scans' in which it did before but still shows those with 'scan' which I guess it would as the * is still at the front.

Any reasons why you think the Like '* CAN *' is not bringing up the records with CAN in it?

Thanks again,

Katherine

 

[BobJacksonNCI]

Katherine,

I've marked this thread because I'm interested in learning what the gurus know about this.

But, I understand the physical problem. If the word CAN is the first word, then there's no preceding space. Likewise, if it's last there may not be a trailing space.

You need to post the characteristics of the field you're searching. Like is it text or memo. If text, someone here will probably post how to identify records that begin with CAN and records that end in CAN. (May have to deal with a trailing period...)


HTH,
Bob

 

[KenReay]

Hi

exactly Bob

how about

Like 'CAN *' OR Like '* CAN *' OR Like '* CAN.*'



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[WMDANIEL]

If you use the Like SQL operator it will return all ocurrences of a word or phrase. If you use the EXACT SQL operator it will return exact matchs only.

So, you may want to give the user a choice (I am assuming that this is part of a form) using a radio button or a check box. For example,

If keyword_search then
SELECT * from SomeTable WHERE VALUE LIKE FORM!FIELD
ELSE IF EXACT_SEARCH THEN
SELECT * FROM SomeTable WHERE VALUE EXACT FORM!FIELD
END IF

You can take care of the syntax, however this general approach will operate based on the users selection of keyword or exact phrase search.

 

[Loktar]

To expand on what people have already said, if the word CAN could be in the middle of a string of characters (like it seems it is) you might need to think of putting some sort of delineator inbetween the words. A space is what people have been using in the examples before, but any character not used in your words will work. Then the field you are searching would look like this:

" scan can candy " or
"-scan-can-candy-"

Then you could do queries such as

Like "* can *"

Like "*-can-*"

Make sure you put the delineator before the first word and after the last word or it won't work. If your data isn't in that format already, you could use a function to populate a query with formatted data.

Hope some of this helps point you in the right direction.

 

[WMDANIEL]

-- Drop the EXACT in my previous post...I was thinking of something else...replace it with the "=" sign for exact matches.

Here is a snippet of code:

DIM SQLTXT AS STRING
CONST Asterix = "*"

-- This matches any instance of the value in control (your search field)

SQLTXT = "SELECT * FROM SOMETABLE WHERE Description LIKE '" & Asterix & frm![control] & Asterix & "'"

-- This matches exact values in description that match the value in your control (your search field)

SQLTXT = "SELECT * FROM SOMETABLE WHERE Description = '" & Asterix & frm![control] & Asterix & "'"

 

[GHolden]

Hi,

Additional information on LIKE.

You can use the following to build up quite complex criteria

? Any single character.
* Zero or more characters.
# Any single digit (0–9).

• Any single character in clist.
  [!list] Any single character not in list.
  with the lists you can also use the '-' character for ranges
  [a-e] would be any one of the following a,b,c,d,e
  
  
  eg.
  
  To find strings starting with a number
  
  LIKE '#*'
  
  or not starting with A,B, or C
  
  LIKE '[!A-C]*'
  
  
  UK National insurance numbers are of the format
  
  ll nn nn nn l
  
  (l = letter, n = number)
  
  which could be changed to
  
  LIKE '?? ## ## ## ?'
  
  I tend not to use the ? and #
  (for compatability with other versions of SQL)
  (also note that SQL Server for example uses % instead of *
  and ^ instead of !)
  
  Instead I use [A-Z] for letters [0-9] for numbers
  
  so the above would be...
  
  LIKE '[A-Z][A-Z] [0-9][0-9] [0-9][0-9] [0-9][0-9] [A-Z]'
  
  UK postcodes can be one of the following
  
  ln nll
  lnn nll
  lln nll
  llnn nll
  llnl nll
  
  (l = letter, n = number)
  
  which could give you the following to find records with
  invalid postcodes.
  
  Format1 = "[A-Z][0-9][ ][0-9][A-Z][A-Z]"
  Format2 = "[A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]"
  Format3 = "[A-Z][A-Z][0-9][ ][0-9][A-Z][A-Z]"
  Format4 = "[A-Z][A-Z][0-9][0-9][ ][0-9][A-Z][A-Z]"
  Format5 = "[A-Z][A-Z][0-9][A-Z][ ][0-9][A-Z][A-Z]"
  
  
  SELECT * FROM tblPostcode
  WHERE
  POSTCODE NOT LIKE Format1 AND
  POSTCODE NOT LIKE Format2 AND
  POSTCODE NOT LIKE Format3 AND
  POSTCODE NOT LIKE Format4 AND
  POSTCODE NOT LIKE Format5
  
  
  One last one (nicked from Microsoft examples)...
  
  if you wanted to find any of the following names... Carson, Carsen, Karson, or Karsen
  you could use...
  
  LIKE '[CK]ars[eo]n'
  
  Hope this helps.
  
  
  
  There are two ways to write error-free programs; only the third one works.

 

[jdmd88]

Hope this works for you but it worked for me:


Like "*"+" men "+"*

 

[jdmd88]

Sorry change men to can

Like "*"+" can "+"*"

I used it in my query and it excluded "Comments" "Moments" "ment" "Amen

 

[katherinep]

Hi everyone,

Thanks for all you help! I have tried various different ways and I still haven't got it exactly working, but it is better than I had it!

I am using this option (thanks jdmd88)

Like "*"+"can "+"*"#

If I try it as in the example with a space before the can then it comes up with nothing again!

But if I don't put the space in the it eliminates most of the words like scans.

Thanks again everyone!

Katherine