String Replace using SQL/TADOQuery
Posted on 2006-11-21
I am working on an application developed in Delphi 2006 (for Win32 platform) that uses MS Access database as a backend. I need to write a query along the lines of:
UPDATE tblPlans SET tblPlans.AccessList = REPLACE(AccessList,"OldValue","NewValue")
WHERE (tblPlans.AccessList LIKE "%OldValue%")
The query works perfectly in MS Access (ie after substituting '*' for the '%' etc), but when run from the Delphi app, results in a message like "Undefined function 'REPLACE' in expression." So it looks like TADOQuery doesn't support the REPLACE function.
Can anyone suggest an alternate approach for replacing text that will work with an UPDATE query (via TADOQuery)?
PS Stored procs aren't an option in the context of this particular application and the nature of it's deployment. (Otherwise I'd happily use them!)
PPS I can get around this problem to a degree with a long formula that uses LEFT, INSTR and RIGHT (ie to grab the strings to the left and right of the "OldValue" and join these with the "NewValue"), but I'd prefer something a little more efficient.