title: How to get the value between 2 string in oracle sql
body: <p><img src="https://i.stack.imgur.com/mO6SJ.png" alt="enter image description here">I have a SOAP_MONITORING table which has RESPONSE_XML column which is <code>CLOB</code> datatype and consist of large string. I have requirement to fetch and show all the SUBSCRIPTION_ID which is hidden in this string. The SUBSCRIPTION_ID resides in this string : <code>&lt;ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"&gt;&lt;ax2130:id&gt;201411211617575057&lt;/ax2130:id&gt;</code> . I have to get all ID which is nothing but my SUBSCRIPTION_ID which resides in between <code>&lt;ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"&gt;&lt;ax2130:id&gt; and &lt;/ax2130:id&gt; string</code>. I tried the below query :</p>
  
  <pre><code>   SELECT REPLACE(REPLACE(MatchedId, '&lt;ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"&gt;&lt;ax2130:id&gt;', ''), '&lt;/ax2130:id&gt;', '') 
  FROM
  (
  SELECT   REGEXP_SUBSTR(RESPONSE_XML, '&lt;ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"&gt;&lt;ax2130:id&gt;\d+&lt;/ax2130:id&gt;') 
  FROM  SOAP_MONITORING 
  )
  WHERE 
  WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder'
  </code></pre>
  
  <p>But received an empty result.</p>