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.

 

[Imaginecorp]

Oh; Sorry... Cast() would not be accurate...

 

[Imaginecorp]

OK, lets try this again. With a SCAN its easy but I would do it like this with a select statement:

STORE "-_=+[{]}\!|;:',<.>/?"+'"'+"' '" to nchar
** you could loop using chr() to build the string

STORE REPLICATE("~",23) TO nc
** we know there are 23 of these things

SELECT *,CAST(chrtran(notes,nchar,nc) as memo) as note1;
FROM ALIAS() into cursor temp

store "~cart~" to cfind
SELECT * FROM temp WHERE cfind $ note1 into cursor finaltemp

 

[Mike Lewis]

It's interesting that ten forum members have given a variety of suggestions, and yet there hasn't been a word of comment or feedback from the person who asked the question.

Shall we assume that she is busy trying out all these ideas and so hasn't had time to reply?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Imaginecorp,

now I understand what you meant with CAST, but you'd not need to cast the modified memo as a new memo, you'd not need to create a temp cursor just to then search "~cart~" in it, you could simply do it in one step like:

nc = ...
nchar = Space(23)
SELECT * FROM table WHERE " cart " $ " "+chrtran+" " (text1,nchar,nc) into cursor result

I'd still vote for a fullword function, not only to handle NULL values, I'd say this would be no problem anyway, but there's another thing that came to mind during the day: what if the word is in the text, but hyphenated? cart is a bad example, but if you search for example "internet", and a text contains inter-net with a line break in between "inter-" and "net" the situation get's more complicated.

Then you'd need to remove CR and LF and Whitespace at the end and beginning of lines, to find such hyphenated words and still would need to keep hyphens, where they are part of the normal syntax of the word, like editor-in-chief or merry-go-round. On the one side, this problem can easily be solved in taking out the hyphens from the searched word too, but there are surely more situations not thought of.

To have a decent fullword() function would be a good foundation to create a fulltext index, by parsing a text to it's single full words.

Bye, Olaf.

 

[Olaf Doschke]

You might think chrtran() removing CR,LF and "-" will do the trick regarding hyphenated words, but that would not work if you'd have a formula in a text like 7*week-days and wouldn't want that text to be found in a search for "weekdays". Admitted it's a curious example. Another problem would be whitespace after the hyphen of a hyphenated word and before the line break, eg "inter-"+space(2)+chr(9)+chr(13)+chr(10)+"net".

All in all the chrtran solution is close enough to being acceptable. And yes, a first search for the word without delimiters being contained is helpful, as it reduces the amount of processing, but you still can do that in the same SQL-Select, as boolean algebra is optimized in VFP, and if a first condition is "cart" $ text1, and that is .F., the second condition isn't evaluated at all anyway.

Bye, Olaf.

 

[Olaf Doschke]

Local lcDelimiters, lcSpace, lcWordSearched, lcWordSearched2
lcWordsearched = "cart" && or "internet" or "editor-in-chief"...

lcDelimiters = chr(9)+'~*-_=+[]{}()\!|;:",<.>/?'+['] 
lcSpaces = space(len(lcDelimiters))
lcDelimters=lcDelimters+Chr(13)+Chr(10) && these are removed because there is no space within the lcSpaces string at the same position.

lcWordSearched2 = ' '+chrtran(lcWordsearched,'-','')+' '

SELECT * FROM table WHERE lcWordsearched $ text1 AND lcWordsearched2 $ ' '+chrtran(text1,lcDelimiters,lcSpaces)+' ' into cursor result

 

[EmmaLu]

Hello all. I just wanted to let you know that I have been trying many of your suggestions to see which will work best for my app. Unlike a Google search my clients will know right away if the query results aren't completely accurate. As I thought, there is much to consider. Thanks for all the great ideas.

 

[Olaf Doschke]

Local lcDelimiters, lcSpaces, lcWordSearched, lcWordSearched2

lcWordsearched = "cart" && or "internet" or "editor-in-chief" or whatever

lcDelimiters = chr(9)+'~*-_=+[]{}()\!|;:",<.>/?'+['] 
lcSpaces = Space(Len(lcDelimiters))
lcDelimters = lcDelimters+Chr(13)+Chr(10)
* adding chr(13) and chr(10) after generating lcSpaces. This way they are removed because there is no space within the lcSpaces string at the position of chr(13) or chr(10) within lcDelimiters.

lcWordSearched2 = ' '+ChrTran(lcWordsearched,'-','')+' '

SELECT * FROM yourtable;
WHERE lcWordsearched $ text1 AND lcWordsearched2 $ ' '+chrtran(text1,lcDelimiters,lcSpaces)+' ';
INTO CURSOR curResult

Bye, Olaf.

 

[Imaginecorp]

Hello Olaf; glad to see you joined in. I am in the process of building a search utility which will not only find text in a tables memo field (5000 or so records normal), but will display the selected records and then highlight every instance of the searched word in each memo. it will also move to the next record once it cannot find the word anymore in the first...It also will provide the capability to search by whole words, Match Cases as well as wild card

I had stopped working on it till Emmalu post reminded me, I had to finish it. Now I am back in it.

Finding the word is not the problem; but have just now encountered the white spaces and blank lines problem when highlighting the found word. The selstart is off because of this... am working on it... any suggestions

 

[Olaf Doschke]

Hi Imaginecorp,

I don't understand "the selstart is off because of this".

If you mean the position of your selection is off, that would be normal if are you using the AT() position from the search in the modified memo and if you not only replace chars, but also remove CHR(13) and CHR(10) like I did.

The way I did it in fact doesn't help much, as the - should also be removed and not be replaced by a space to find unhyphened words. And also the speed up doesn't help much in this case, the first part of the where clause would only find memos containing the word as given by the user, which removes the cases of hyphenated word anyway.

Solving the problem of maintaining the position of words in the modified memo means not removing any char, only doing replaces. but then unwanted chars like the hyphen and whitespace or CRLF between hyphen and the rest of the word would remain anyway.

If you replace white space and CRLF in the middle of hypenated words with "-" instead of with "~", you could do regular expressions that allow any number of "-" in between the letters of the word, but still needing a "~" before and after it.

Or you calculate the adjustment of the positions you need to do. I don't have an immediate idea for that one.

Bye, Olaf.

 

[Imaginecorp]

Hello Olaf;

Yes I found that out about CHARTRN(), not using that any more as you pointed out, the position in the modified memo was not matching the regular memo.

But I figured it out by using STRTRAN(). I am scanning each memo field. The user enters the search string in a textbox, my find button looks for any punctuation marks in it first, and then it stores the memo value into a variable, replaces all punctuation marks, except the ones the user entered, with the tilde "~" then does the search. This is for the Exact word and Case match, Wild card is simple… So far it’s working like a charm. Putting the finishing touches now...

 

[Mike Lewis]

EmmnaLu,

Good to hear that you're finding these suggestions useful.

Looking back over this long thread, I think that SnyAc's suggestion is the most promising - although I think I'd get rid of the SCATTER MEMVAR MEMO and just copy the memo field into a variable instead. The code cannot really be optimised, but it should still be tolerably fast, especially if only a few records contain the target search term.

Let us know which solution you eventually go with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Hi Imaginecorp,

I still don't understand your problem.

You surely know the difference of CHRTRAN vs STRTRAN. I intentionally made CHRTRAN have less spaces in lcSpaces than chars in lcDelimiters, to actually remove CHR(13) and CHR(10) as an attempt to also find hyphenated words. I don't know if you understood that and what STRTRAN ahs to do with it, as it's working totally different for more than one char.

Also STRTRAN can reduce or add to the length, if that's a problem make sure your replacement strings are as long as the search strings.

Bye, Olaf.

 

[EmmaLu]

Hi Imaginecorp,

I would be very interested in how you highlight every instance of the searched word in the results screen memo field. I have been able to highlight the first occurrence using a combination of the AT() function and KEYBOARD "{RIGHTARROW}" and KEYBOARD "{SHIFT+RIGHTARROW}" but it seems like there should be a better solution.

Thanks EmmaLu

 

[Imaginecorp]

Its all done and works 100% accurate.

Much more complicated than a simple Select statement... Its a self contained class for Notes which can be dropped on any form.

It provides the capability to add, delete, edit and Find (words in) Notes. It has a listbox that displays the dates the notes were created and an editbox that displays the notes. The listbox is used to display the appropriate notes for the date selected, As opposed to a single memo field which can contain years of notes which nobody can make any sense of and one of my pet peves.

It woks exactly like CTRL-F but better, it first changes the display of the editbox and the listbox to display Only the records that contain the searched text.

It highligts the first occurance of the text in the editbox and when "Again" (Find button changes to Again)is clicked the next occurance is highlighted and so on, as opposed to highlighting All occurances as VFP help (search) does. Once all occurance are shown in the edit box it traverses to the next record and the highlighting process starts again.

Once Cancel is clicked All the records are redisplayed back...

 

[Imaginecorp]

Hello Emmalu;
Keboard etc. do not work at least IMO. Its a little more complicated than that but not too much

First store the length of the text to be searched, prefreably in a form property also create an array as a property of the form.

Once you have found the text, store its position (ATC()) in the array. When the record that contains the found text is displayed, do the following:

With .reg_editbox1  
  .Refresh
  .SelStart = (This.Parent.searcharray[this.ItemData(this.ListItemId)]-1)
  .SelLength = This.Parent.nLengthOfExpression
  .SelectedForeColor = Rgb(255,0,0)
  .SelectedBackColor = Rgb(0,0,0)
  .SetFocus()
Endwith

Itemdata[] is also an array that is part of the listbox

 

[Olaf Doschke]

You can also highlight multiple instances at once using rtf. While something that scrolls to the found positions can be better you can also combine an rtf highlighting with this positioning like in CTRL+F "next".

Bye, Olaf.

 

[Imaginecorp]

You can also highlight multiple instances at once using rtf

Never liked that, could not tell you why, just think it is too untidy...