Alternative for nesting REPLACE()?
Is there an alternative for nesting REPLACE() functions when replacing multiple strings? Even a simple removal of CR and LF results in REPLACE(REPLACE(data,CHAR(13),'~'),CHAR(10),'~'). Now imagine a dozen replacements .....
Write your own function.
Here's an aggregate replace function I wrote several years ago.
Disclaimer: I was relatively new to C when this was written, so no guarantees except for the fact that I've used it over the years and have not encountered any issues.
Bedsides Simon's good suggestion of writing a custom function, you might consider whether the expected rows would fit in cache, and, if so, a series of separate UPDATE's enclosed in a transaction might be sufficiently speedy.
-- or, a CTE can be used with a table of search/replace strings.
-- or would just formatting a big UPDATE not meet your need? I imagine a dozen replacements as something like:
UPDATE myTable SET data =
CHAR(13), '~' )
,CHAR(10), '~' )
,'division', 'unity' )
,'fear', 'serenity' )
,'greed', 'charity' )
,'travesties', 'justice' )
,'suspicion', 'empathy' )
,'depression', 'joy' )
,'hate', 'love' )
,'poverty', 'sharing' )
,'copyright', 'blessing' )
Unfortunately you are barking up the wrong tree...... This was never about UPDATEs - the usage of REPLACE is intended for cleaning up output and/or fixing issues with UTF8 and ORDER. And nesting REPLACE does not work - by the time I had reached the required level of nesting I was rewarded with a stack overflow.
Have a small table with the replacements and use a simple
SELECT to get the corresponding value.
I think this is safer because it will only replace a value once.
REPLACE runs the risk of accidentally replacing what's already replaced, unless that's a requirement in some cases.
For example, changing 'dogs' to 'cats' and 'cats' to 'mice' with nested
REPLACE could end up with just 'mice' instead of 'cats' and 'mice'.