Well... it certainly could be a step in the right direction, but I'm not quite there yet. Here's the result set I'm getting:

ID SomeString NEWVAL

--------------------------------------------------

1 L@*K@*H@/324 AT@*K@*H@/324

2 (AF#-AR#)*(Y#+BB#) (AF#-AR#)*(Y#+BB#)

3 TT#*(L#+D@)*C@ TT#*(L#+D@)*C@

4 ((AE#-AR#)*(Y#+A#))/Y# ((AE#-AR#)*(Y#+A#))/Y#

1 L@*K@*H@/324 L@*K@*H@/324

2 (AF#-AR#)*(Y#+BB#) (AF#-AR#)*(Y#+BB#)

3 TT#*(L#+D@)*C@ TT#*(L#+D@)*C@

4 ((AE#-AR#)*(Y#+A#))/Y# ((AE#-AR#)*(Y#+A#))/Y#

1 L@*K@*H@/324 L@*K@*H@/324

2 (AF#-AR#)*(Y#+BB#) (AF#-AR#)*(Y#+BB#)

3 TT#*(L#+D@)*C@ TT#*(AT#+D@)*C@

4 ((AE#-AR#)*(Y#+A#))/Y# ((AE#-AR#)*(Y#+A#))/Y#

The bad news is that there are twelve rows instead of four. The good news is that one of those three copies contains the replaced variable. So the bad news is easily eliminated by altering the SELECT and making it into an additional CTE:

,cte_Replace AS

(select

MyTable.* ,

REPLACE(SomeString,oldval + MyFn.fnval ,newval + MyFn.fnval ) AS NEWVAL

FROM MyTable

CROSS JOIN MyFn

CROSS JOIN MyReplaceMents)

SELECT * FROM cte_Replace

WHERE SomeString != NEWVAL

This excludes records where no variables were replaced, but that's fine. They can be discarded and it won't affect my overall task.

The first problem is that if I try to do more than one replacement in the same pass:

MyReplaceMents (oldval,newval)

AS

(

SELECT 'L','AT'

UNION

SELECT 'AR','ZZ'

),

...any row that contains two replacements appears twice in the result set, once for each replacement, and each row containing only one of the replacements. But I suppose that could be resolved with multiple iterations. That would certainly be less painful than iterating through each formula string character-by-character.

The larger problem is that when I change the second formula to '(AL#-AR#)*(Y#+BB#)' to test the "do not replace" condition, the AL# becomes AAT#. So it's not discriminating based on a preceding character, which is the primary requirement. Without it, we basically have the same results as if we'd used a simple REPLACE() function.

The more I consider this, the less convinced I am that there's a solution to this that involves REPLACE(). But I haven't given up hope yet.

Thank you very much for your efforts, Lowell. I'll dig more into your suggestion and see if there's a tweak that can be made to overcome the "do not replace" problem.

Thanks!

Ron