>I have a situation where I am using STRTRAN to replace some text in a memo field. It goes like this:
>
>REPLACE mymemo WITH STRTRAN(mymemo,'my text','new text')
>
>But, sometimes, in the memo field, the text that need to replaced is either lower case, upper case or a mix of both.
>
>If I use something like this:
>
>REPLACE mymemo WITH STRTRAN(UPPER(mymemo),'MY TEXT','new text')
>
>it will replace all the variations of my text but will also replace the memo field in uppercase. How, can I get all the variations by keeping the memo field as is?

Michel:

I tried this and it worked!

select substr(notes,at("TO CREATE",upper(NOTES)),9) as "NoteString" ;
   from badmemo where upper(notes) like "TO CREATE"

What I did was create a table with a memo field called NOTES. I put in some text that had TO CREATE in various upper/lower case. The select statement created a cursor with a memo field called "NoteString" with all the various ways I typed in "TO CREATE". You could then scan the cursor and issue a replace statement against the original table for each case variation in the cursor and still maintain the case of the remaining text.

Where it fails: it only picks up the first instance of the string in the memo field, so you may not get all case variations. Possible workaround is to replace the selected text with some dummy text such as "XXXXXXXXXX" (a string not likely to ever appear anywhere in the memo field). Issue the select again which would pick up the 2nd instance. Keep up this iteration until the select returns 0 rows. The final STRTRAN would be to replace all the "XXXXXXXXXX" with the real replacement string.