Find exact matching text

[EmmaLu]

I want to search for exact matching text in a character or memo field. Is there a way to do that in VFP. For example I want to create an SQL statement that will select all records from a table with the word "cart" in the field text1. But I DON'T want the results to include records with "golfcart" or "cartoon" in the text1 field.

 

[TamarGranor]

Yes, there are a couple of ways to do what you want. The preferred choice is to use the == operator, which means "exactly equal."

SELECT * ;
  FROM YourTable ;
  WHERE text1 == 'cart' ;
  INTO CURSOR YourCursor

 

[Stella740pl]

Tamar, I think EmmaLu wants to find memo fields which contain the exact whole word "cart" and exclude those where "cart" is a part of other words.

 

[Imaginecorp]

Yes but its a 2 step process if you want to use the SQL Select statement.

This is how you do it. I am assuming you have the CAST() function in your version of VFP.

Step 1. SELECT * , CAST(STRTRAN(notes," ","~") as memo) as note1 FROM ALIAS() into cursor notestemp
*** notes is the memo field and Alias() is your table name

Step 2:
store "~Golf~" to cFind
select * from notestemp where cFind $ note1 into cursor finalnotes

 

[Imaginecorp]

Keep in mind, you cannot use the Note1 memo field, as its full of the "~" character but rather the original fetched notes memo... Kinda obvious but just thought I would put in this reminder...

 

[Mike Gagnon]

Here

local lnCount,lnCount2
store 0 to lnCount,lnCount2 
create cursor myCursor (text1 m)
insert into myCursor (text1) values ('golf golfcart')
set library to foxtools addi
lcVar = myCursor.text1
lnCount= words(lcVar)
for i = 1 to lnCount
   if wordnum(lcVar,i) ='golf'
     lnCount2 = lnCount2 + 1
   endif
endfor
messagebox("We have :"+transform(lnCount2))

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Mike Gagnon]

Disregard my suggestion.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[JRB-Bldr]

Another somewhat 'kludgy' way would be to anticipate the variety of ways that 'cart' (by itself) could appear in the text and use those combinations as part of the query.

For example:

* --- Possible Combinations =  " cart ; cart.; cart,;" ---

SELECT * ;
  FROM YourTable ;
  WHERE (' cart ' $ LOWER(Text) ;
    OR ' cart,' $ LOWER(Text) ;
    OR ' cart.' $ LOWER(Text));
  INTO CURSOR YourCursor

Admittedly it is a sort of 'messy' approach, but depending on how limited the usage of the word might be it could work. And it would exclude 'Golfcart', 'Cartoon', 'Carthage', etc.

Good Luck,
JRB-Bldr

 

[Stella740pl]

JRB-Bldr,

That's what I was thinking about (and do once in a while - interactively, not in a program, though) - and didn't want to mention.

But that solution should also consider 'cart' being the first or the last word:

SELECT * ;
  FROM YourTable ;
  WHERE (' cart ' $ LOWER(Text) ;
    OR ' cart,' $ LOWER(Text) ;
    OR ' cart;' $ LOWER(Text) ;
    OR LEFT(LOWER(Text),5)=='cart ' ;
    OR RIGHT(LOWER(Text),6)==' cart.' ;
    OR RIGHT(LOWER(Text),6)==' cart?' ;
    OR RIGHT(LOWER(Text),6)==' cart!') ;
  INTO CURSOR YourCursor

Still, not all the possible combinations are mentioned.

I guess, some solution with Foxtools library might be the way to go, but don't have anything installed at home, so cannot look up if the right one exists.

 

[GriffMG]

I suppose you could do something like this:

copy to TmpTable for (" CART "$" "+CHRTRAN(UPPER(Text)," 1234567890!,;.#'^&%$£/\*()[]{}#~@=-+"+chr(34),space(255))+" ")

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

If this really has to be done with a SQL SELECT, I'd either go for Stell'a solution, or something similar but with the LIKE operator:

SELECT * ;
  FROM YourTable ;
  WHERE LOWER(Text) LIKE '% cart %';
    OR LOWER(Text) LIKE '% cart.%';
    OR LOWER(Text) LIKE '% cart,%'; && etc. etc.
  INTO CURSOR YourCursor

But either way, you'd have to anticipate every possible combinination of delimiter characters, on either side of the search term, which makes the whole thing a bit unweildy.

But if you were willing to use native Foxpro syntax, it becomes much more feasible. Something like:

lcDelims = ".,?!()"  && etc. etc.
SELECT YourTable
SCAN
 FOR lnI = 1 TO GETWORDCOUNT(Text, lcDelims)
   IF LOWER(GETWORDNUM(Text, lnI, lcDelims)) == 'cart'
     * This is a hit. Process it in any way you like.
     EXIT
   ENDIF
  ENDFOR
ENDSCAN

Obviously, you substitute your search term for 'cart' in this example.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

I'd perhaps accept the fact, that this takes a little longer in querying and use a user defined function to find standalone words in a given text. Lets call that fullword(text,"word"), then this would simply be

SELECT * FROM Table Where fullword(text1,"cart").
It's not rushmore optimizable, as it's a memo field anyway.

And then it's up to the implementation of that function, what delimiters are accepted etc.

If you want a better performance you better build up some meta data about the memo fields in question and seperate all words into seperate records of a word list pointing back to the record id, some kind of fulltext index.

My suggestion for a fullword function would simply be:

Function fullword(tcText,tcWord)
#Define ccDelimiters ",.;-!?" && add delimiters I didn't think of (other than space)

IF ISNULL(tcText) OR ISNULL(tcWord)
   Return .F.
ENDIF

IF Vartype(tcText)#"C" OR Vartype(tcWord)#"C"
   Return .F.
ENDIF

If NOT (" "+Upper(tcWord)+" " $ " "+Upper(Chrtran(tcText,ccDelimiters," "))+" ")
   Return .F.
Endif

Return .T.

Bye, Olaf.

 

[Olaf Doschke]

If NOT (" "+Upper(tcWord)+" " $ " "+Upper(Chrtran(tcText,ccDelimiters,space(len(ccDelimiters))))+" ")
Return .F.
Endif

Or take Griff's code, may still SQL-Select instead of COPY.

Bye, Olaf.

 

[brigmar]

An alternative; regular expressions.

re1 = CREATEOBJECT("VBSCRIPT.REGEXP")
re1.Pattern = "\bcart\b"
re1.IgnoreCase = .T.

?re1.Test("cart")
?re1.Test("horse and cart")
?re1.Test("cart and pony")
?re1.Test("cartoon")
?re1.Test("golfcart")
?re1.Test("encarta")

 

[Mike Lewis]

Brigmar,

Your suggestion sounds promising, but you'd have to make the pattern a little bit more sophisticated. Something like "[^A-Za-z]cart[^A-Za-z]".

It would be horribly slow, of course, but then so would some of the other suggestions.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[bborissov]

Based on Tamar's code:

*** Case insensitive
SELECT * ;
  FROM YourTable ;
  WHERE ' '+LOWER(text1)+' ' LIKE '% cart %' ;
  INTO CURSOR YourCursor

*** Case sensitive
SELECT * ;
  FROM YourTable ;
  WHERE ' '+text1+' ' LIKE '% cart %' ;
  INTO CURSOR YourCursor

Both are NOT optimizable.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SnyAc]

The best solution regarding speed would be a combined technique solution.

1) SQL select for the search word to be contained in the memo field.

2) scan the selected records and parse the memo field for exact matches on the word.

SELECT * from YourTable ;
  WHERE .F. INTO CURSOR yourCursor READWRITE
SELECT * ;
  FROM YourTable ;
  WHERE atc('cart',text1)#0 .and. !deleted() ;
  INTO CURSOR tmpCursor
LOCAL lnTmp
IF _tally > 0
  SCAN
    FOR lnTmp = 1 to GETWORDCOUNT(text1)
      IF UPPER(GETWORDNUM(text1, lnTmp)) == 'CART'
        SCATTER MEMVAR MEMO
        INSERT INTO yourCursor FROM MEMVAR
        EXIT
      ENDIF
    ENDFOR
  ENDSCAN
  USE IN tmpCursor
ENDIF

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc. a division of AmTech Software

http://www.hyperwareinc.com

 

[Imaginecorp]

Amazing...Using a sledge hammer to swat a fly
Combinations, VB code, functions, spaces, non spaces, commas, keep your fingers crossed etc etc. When a simple CAST() will do it for you with no guess work and is 10 times faster than anything suggested so far…The biggest joke is the tilde “ ~ ” idea is from VFP Code reference dialog box.

 

[Mike Lewis]

Imaginecorp,

You're basically correct, but you'd have to do more than replace the spaces with the tilde. The problem (that all the solutions are trying to tackle) is that the target word can be delimited by a variety of punctuation signs and symbols, not just spaces.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Stella740pl]

Imaginecorp,

I just noticed your solution after I read your last post - I can bet it was not there last night when Mike Gagnon's solution appeared (didn't have a chance to look at it, as I was leaving already - and then later noticed his "disregard" post).

I cannot try it, as my VFP6 doesn'r have CAST(), but I don't understand. Basically, you are replacing all the spaces with tildes, then looking for "~word~" expression? Why? If it was this simple - only space would be a word delimiter - not even CAST() would be needed, you can look for " word "$text, or using an AT() function, and you are done. The problem is that not only space can be a word delimiter, but a whole army of syntax and special symbols.
If I am missing something here, please explain.