Subject Re: Replacing a substring From Mervyn Bick <invalid@invalid.invalid> Date Mon, 24 Jul 2023 11:40:46 +0200 Newsgroups dbase.getting-started On 2023/07/23 21:53, Ken Mayer wrote: > On 7/23/2023 12:08 PM, Charlie wrote: >> I am wondering how to change text like LI10000 TO LH0000? >> >> I have tried this: >> USE MASTER.DBF >> REPLACE substr(part_no,1,2) with "LH" for mfg = "LIONEL H O SCALE" >> >> But come back with error 'expecting with' > > The dBASE REPLACE command assumes you're replacing the full contents of > the field, so there may be an issue there. I don't think you can do what > you're trying that easily. I could be wrong (and I expect if I am, > Mervyn will pop in with something). I didn't fully read your message before replying to Charlie so I missed the bit below.  Caffeine deprivation. :-) > You might want to try the SQL UPDATE SET command, but am not sure it > would work either, because local SQL wouldn't understand the SUBSTR() > function. localSQL is one of dBASE's better kept secrets. :-)  It has a bit of a learning curve and the help file is well hidden away in the C:\Program Files (x86)\Common Files\Borland\BDE folder.  It will, however, work like a charm in this instance.  Actually, as the OOP query object is basically built around SQL it behooves (lovely word :-) ) programmers to get acquainted with localSQL.  Even given the limitations of localSQL, which is a small sub-set of "proper" SQl, it can make life easier if one uses it. In place of substr() localSQL has substring(exp FROM x TO y).  Like substr() in dBASE,  omitting the last parameter will simply include all the remaining characters. UPDATE master SET part_no = 'LH' + substring(part_no from 3) WHERE mfg = 'LIONEL H O SCALE' As an aside, I've used single quotes from force of habit as this would be required for "proper" SQL.  localSQL is less fussy and double quotes can be used.  Square brackets, which dBASE will also accept as string delimiters, can not, however, be used as string delimiters in a localSQL command. Mervyn