title: Replace string parts that appear twice Oracle
body: <p>I am trying to work out in Oracle how to isolate/highlight word combinations in a concatenated string like the one below:</p>
  <pre><code>Some words##Again words##More of this||@@@@||Some words##Again words##Other
  </code></pre>
  <p>The idea is to find the word combinations that appear <strong>exactly twice</strong> and replace them by 0 so I'm left with the ones that appear only once, either on the left side of the <code>||@@@@||</code> or on the right side. The result of the query should be something like this:</p>
  <p><em>Highlighted</em></p>
  <pre><code>Some words##Again words##More of this||@@@@||Some words##Again words##**Other**
  </code></pre>
  <p><em>Replaced</em></p>
  <pre><code>0##0##More of this||@@@@||0##0##Other
  </code></pre>
  <p>To give you some more information about the concatenation: the left side (before the <code>||@@@@||</code>) is my current customer record, while on the right hand side I have the previous version. By making the replacements I can reveal any differences between customer records.</p>
  <p>I have tried to get this done by using:</p>
  <ul>
  <li><strong>regexp_replace</strong>: this does not work entirely with <code>REGEXP_REPLACE(MY STRING,'((Some words){1,2})|((Again words){1,2})','0',1,0)</code> as for some reason the string parts in my first record are never correctly replaced. I'm also hitting the limits of this function due to the number of word combinations I need to match;</li>
  <li><strong>nested CASE WHEN</strong>: does not work either obviously as CASE WHEN - even nested - stops when the first match is found but I need to have all conditions checked and replaced.</li>
  <li>I have thought about using subselects, but as this query uses one of the largest tables in my schema, this will not be usable except on a per customer basis. And it might still not work...</li>
  </ul>
  <p>Some more information in order to find a solid, performant solution:</p>
  <ul>
  <li>I have 34 possible word combinations to match</li>
  <li>I have no idea which ones will be there, ever, except when I run the query obviously</li>
  <li>I have no idea in which order they will be in the concatenated string</li>
  </ul>
  <p>I hope this is clear. Anyone with some magical ideas?</p>
  <p>Thanks in advance</p>