Alternative for nesting REPLACE()?
(1) By anonymous on 2020-10-04 13:35:55 [link] [source]
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 .....
(2) By Simon Slavin (slavin) on 2020-10-04 15:11:10 in reply to 1 [link] [source]
Write your own function.
(3) By Donald Griggs (dfgriggs) on 2020-10-04 16:07:55 in reply to 1 [link] [source]
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' )
(4) By anonymous on 2020-10-04 20:25:32 in reply to 1 [link] [source]
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'.
(5) By anonymous on 2020-10-31 22:51:57 in reply to 2 [link] [source]
(6) By Simon Slavin (slavin) on 2020-11-01 08:53:34 in reply to 4 [link] [source]
Starting with that idea of a small table with the replacements, might it be possible to do this using a CTE ?
(7) By anonymous on 2020-11-01 22:05:50 in reply to 3 [source]
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.
(8) By jake on 2020-11-02 10:03:58 in reply to 2 [link] [source]
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.
(9) By mixcocam on 2022-09-22 16:23:40 in reply to 6 [link] [source]
Yes it's possible to do this with a CTE. I had to include a constraint in the "WHERE" statement to avoid double entries.
In my case I'm having to do string manipulation for date fields.
WITH replacements(original, changed) AS (VALUES ('Jan', '01'), ('Feb','02')) SELECT sku, REPLACE(date_time, original, changed) as new_date FROM sc_usa LEFT JOIN replacements WHERE new_date != date_time LIMIT 20