SQLite Forum

Alternative for nesting REPLACE()?

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]

(5) By anonymous on 2020-10-31 22:51:57 in reply to 2 [link] [source]


(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.

(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 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( data, CHAR(13), '~' ) ,CHAR(10), '~' ) ,'division', 'unity' ) ,'fear', 'serenity' ) ,'greed', 'charity' ) ,'travesties', 'justice' ) ,'suspicion', 'empathy' ) ,'depression', 'joy' ) ,'hate', 'love' ) ,'poverty', 'sharing' ) ,'xenophobia', 'friendship') ,'copyright', 'blessing' ) ;


(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.

(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. The nested 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'.

(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 ?

(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'))
	sku, REPLACE(date_time, original, changed) as new_date
FROM sc_usa
LEFT JOIN replacements
WHERE new_date != date_time