Replace in memo fields 2

[linousa]

Have DB with 100K records with multiple memo fields each field have variable data, need to replace all "red" with "blue" in each record including memo fields. Any quick solutions?

 

[JRB-Bldr]

need to replace all "red" with "blue" in each record including memo fields

So where is the "red" ?

Is it in the Memo field(s) or is it in another field?

Regardless, you handle the contents of a Memo field just like you would any other Character field.

For example:

REPLACE ALL MemoFld WITH STRTRAN(MemoFld," red "," blue ") FOR " red " $ MemoFld

Notice that I put spaces around the "red" and matched it for the "blue".
That is so that I did not change other words in the Memo field which contained "red" but were not themselves "red" - so that words like "watered" don't become "wateblue". But you can do whatever you need to do.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

JRB-Bldr has put you on the right track and made a valid point about being careful to not simply STRTRAN any "red" with "blue". There might be situations you have red at the start or end of the memo or other fields, or have red followed by a comma. Besides STRTRAN is case sensitive and you'd not want "Red" to become "blue" in case it's the sentence start. Also don't be tempted to STRTRAN with LOWER(Memofld), that way you do turn all text to lower case. You better address all situations individually.

You can BROWSE FOR "red" $ LOWER(MemoFld) to check, whether reds in ans case are remaining and then clicking the memo to open in a separate memo edit window either do these manually or inspect whether you have cases like mentioned containing red as part of a word. As side note, don't close and reopen the memo edit window, simply keep it open aside of the main browse window and skip/scroll through the records and the memo of the current record will show.

Bye, Olaf.

 

[linousa]

Is it possible just to replace 2nd or 3rd.. line in memo field? Another words is to refer to a certain line in memo field.

 

[JRB-Bldr]

You could always use ALINES() with or without MEMLINES() to put the individual lines of a Memo field into an array.
Then change whichever line you want.
And finally put all of the lines back together and put them back into the Memo field.

Or MLINE() might work for you, but you would have to figure out how to get it back into the Memo field after your change.

Look at these VFP commands in your VFP Help system to get more information on them.

You can always set up a test and try a variety of approaches to find which works best for you.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

You got a good answer, there is no such thing as referring to a line of a memo, you can only copy it out and then changing this copied line of course does only change the memo, if you then put it back. There are no easy line oriented commands like replace line 2 or such. If you want to easily work on the lines of memo fields you better create a new table which relates several records (one for each memo line) to the main record and then have each line as separate record in its own "cell".

VFP has enough string functions, so you can cope with the situation as advised and pull out lines via MEMLINES or ALINES, process them one by one (or just the 2nd,3rd) and put them back together. But no, adressing separate rows is much easier, if you talk of records of a dbf than of text rows of a memo. VFP is a database, not a word processor (besides they orientate on paragraphs, sentences rather than lines).

If you're misusing memo to store data of several rows, then that's how you're "punished" for that. Better create a better data schema for your needs.

Bye, Olaf.