How to locate a specific word in a field? 1

[SummerWarmth]

I have a data base with a field called COMPANY.
I would like to be able to locate any company with the word "FISH" at any location in the field" ie
"THE FISH PLACE", "FISH R US", "WE HAVE GREAT FISH" etc.
Then I would like to locate the next occurance.
I would appreciate any help.

 

[billch]

LOCATE FOR "FISH" $ UPPER(company)

Bill Chambers
Chambers & Associates

http://www.ChambersData.com

 

[JRB-Bldr]

"Then I would like to locate the next occurance."

To determine if the LOCATE was successful you would check the results

LOCATE FOR "FISH" $ UPPER(company) 
IF FOUND()
   * --- Found ---
   < do whatever >

   * --- To find the next you could use... ---
   CONTINUE
   IF FOUND()
   ELSE
   ENDIF
ELSE
   * --- Not Found ---
   < again, do whatever >
ENDIF

An alternative way which would find ALL records, would be to use a SQL Query

SELECT *;
   FROM MyTable;
   WHERE "FISH" $ UPPER(company) ;
   INTO CURSOR Results

SELECT Results
IF RECCOUNT() > 0
   * --- Records found matching criteria ---
   < do whatever >
ELSE
  * --- No records found ---
   < again, do whatever >
ENDIF

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

With SQL you also use LIKE '%FISH%', but that's case sensitive.

Jrbbldr's first LOCATE/CONTINUE logic could be done a little better:

Locate For ...
Do While Found()
   * do whatever
   Continue
Enddo

The loop will only be entered, if an initial record is found and will Continue as long as Continue finds additional records.

The advantage to Jrbbldr's code is you don't repeat the *do whatever.

Bye, Olaf.

 

[falconsight]

I'd recommend the second option from jrbbldr, using a recordset. It is because may be used widely, with almost any database, not only DBF.

 

[Mike Lewis]

If you really want to get FISH as a word, rather than just a character string, consider using GETWORDNUM() and GETWORDCOUNT().

That way, you'll avoid finding words like FISHY and FISHFACE (if that's what you want).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Mike,

yes, but getwordnum would need to be done in a loop over all words, surely nothing you can use in SQL. To avoid anything foxpro specific you'll use LIKE, You could search for ' '+upper(text)+' ' like '% FISH %' or ' '+upper(text)+' ' like '% FISH,%'

And some more: '% FISH!%', '% FISH?%'...

Upper() is also available in SQL Server, still not ANSI. You would rather use a case insensitive collating sequence instead of upper(text)

With a fulltext index you'd be better off.

Bye, Olaf.

 

[Mike Lewis]

Olaf, that's true, but I interpreted the question to mean that he required to find all occurrences of the word in a given string. If, as you assume, he wants to find all records where the string occurs at least once, that's a different story. In that case, I'd agree that the LIKE operator would be a better bet.

By the way, there's no problem in making LIKE case-insensitive in VFP's version of SQL. As he didn't say anything about using a different back-end, I'll assume that what he wants.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Mike, you are right. The initial question was about occurences in "the field". But then a field is part of a table and you'd first find each record or at least a record having the word in it. And then you can process the found texts. If he meant just one string, I would have expected him to talk about a string, not a field (of a table).

And his main concern was to find companies with fish in thier name.

Bye, Olaf.

 

[Mike Lewis]

Olaf: Yes, I agree completely.

This looks like one of those cases where someone posts a questions, gets several useful answers, but never comes back to the forum to acknowledge or comment.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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