title: Converting a string (delimited by consecutive delimiters) to rows using Oracle regular expression
body: <p>I have a generic string delimited by consecutive delimiter tilde (~) in Oracle. For e.g. the string is 'apple~orange~~mango~~grapes'. It need to be converted into rows but one important thing to be noticed is that the separator is consecutive tilde not single tilde. The output should be like below:</p>
  
  <p>apple~orange</p>
  
  <p>mango</p>
  
  <p>grapes</p>
  
  <p>The workaround is already done using instr and substr oracle functions but I need more cleaner solution using Oracle regular expressins. I have tried using below query but not gettig the correct solution:</p>
  
  <pre><code>WITH str AS (SELECT 'apple~orange~~mango~~grapes' str FROM dual),
       cnt AS (SELECT LEVEL sno FROM dual CONNECT BY LEVEL &lt; 5)
  SELECT regexp_substr (str, '[^~]+', 1, sno) FROM str CROSS JOIN cnt;
  </code></pre>