Replace substring in FoxPro query

Question

I've been trying to find some help online about FoxPro queries, but seem to be struggling. The closest I've found to my question is this: Find a Specific Character in a Field and Replace it with a Phrase, but it's not everything I need.

I have a FoxPro database, and we need to change some descriptions for certain records by replacing a word.

I know how to do it in SQL;

UPDATE cname 
SET cname.cn_desc = REPLACE(cname.cn_desc, 'lw', 'lightweight') 
WHERE cname.cn_cat = 'Q'

But I don't know how to replicate this in VFP. I've tried

REPLACE cname.cn_desc WITH STRTRAN(cname.cn_desc, 'lw', 'lightweight') WHERE (cname.cn_cat='Q')

But that just returns unrecognized keyword

score 4 · Accepted Answer · answered Mar 08 '18 at 14:14

update cname where cn_cat = "Q" set cn_desc = strtran(cn_desc, "lw", "lightweight", 1, 1, 1)

The last three parameters mean replace the first occurrence of "lw", only replace one occurrence of "lw", and be case insensitive.

Note that if the expanded value exceeds the 40 char field length which I know cn_desc is ;) then it will truncate it.

score 0 · Answer 2 · answered Mar 08 '18 at 20:54

If you know how to do that in SQL then why not simply use SQL, except changing the function replace() to something VFP specific (remember functions are mostly specific to the backend they are used and not a part of the ANSI SQL).

UPDATE cname 
SET cname.cn_desc = strtran(cname.cn_desc, 'lw', 'lightweight') 
WHERE cname.cn_cat = 'Q'

When you attempt to do the same using xBase commands instead of SQL then instead of 'where' you need to use 'for' :

REPLACE cname.cn_desc WITH STRTRAN(cname.cn_desc, 'lw', 'lightweight') for (cname.cn_cat='Q')

IOW you were very close in both of your tries.

Replace substring in FoxPro query

2 Answers2