SQLite Forum

Alternative for nesting REPLACE()?
Login

Alternative for nesting REPLACE()?

(1) By anonymous on 2020-10-04 13:35:55 [link]

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]

Write your own function.

<http://www.sqlite.org/c3ref/create_function.html>

(3) By Donald Griggs (dfgriggs) on 2020-10-04 16:07:55 in reply to 1 [link]

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:

<code>
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'  )
   ;
</code>

Donald

(4) By anonymous on 2020-10-04 20:25:32 in reply to 1

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

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

Done...

(6) By Simon Slavin (slavin) on 2020-11-01 08:53:34 in reply to 4 [link]

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 [link]

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]

Here's an [aggregate replace function](https://github.com/jakethaw/replace_agg) 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.