seek/locate a specific word in a Memo field

[ms777]

Hi,

I have a dbf with 500,000 records and 2 fields (acctnm, notess). The "notess" field is a Memo type field and I need to loca/seek/find in each record a specific word in that Memo type field. The word being "fraud". How can I extract all recno's with that specific word in the Memo field.

Any help on this?

Thanks,
M7

 

[Mike Gagnon]

select recno() as rec from myTable where "fraud" $ notess

Mike Gagnon

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

 

[GriffMG]

I would add an Upper to that...
perhaps like this:

Select recno() as rec from myTable where "FRAUD"$UPPER(notess)

Regards

Griff
Keep [Smile]ing

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

 

[ms777]

You guys are the best!! Works like a charm. Many thanks.

 

[cbasoz]

LOCAL oRX
oRX = Createobject("VBScript.RegExp")
With oRX
  .Pattern = "\bfraud\b"
  .IgnoreCase = .T.
  .Global = .T.
ENDWITH

SELECT * FROM myTable WHERE oRX.test(notess)

Cetin Basoz
MS Foxpro MVP, MCP

 

[alan92rttt]

Cetin, At first glance that seems like over-kill. But, you normally give really good advice so what am I missing that makes that better than the use of the $ command?



Alan

 

[GriffMG]

Try both, see which is quicker and use the one you like!

B-)

Regards

Griff
Keep [Smile]ing

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

 

[cbasoz]

$ is case sensitive. I wouldn't use it if I were looking for 'fraud', 'Fraud', 'FRAUD' ... If I would opt to use a VFP function anyway then I would use:

where atc('fraud', notess) > 0

I prefer RegExp in memo like searches, because you can define a pattern (which in this case were very simple). With $, atc() or alike functions in VFP a search on 'fraud' would find 'fraud', 'fraudelent', 'afraud' ... when there is a chance that I am only interested in whole words. And one another reason is that such $, atc() ... are VFP specific while you can do RegExp searches on SQL server too - just a side thought

Cetin Basoz
MS Foxpro MVP, MCP