Search code examples
visual-foxprofoxpro

Replace substring in FoxPro query

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

Solution 
  • 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.