title: extract data from clob column in oracle
body: <p>I am having data in clob column as below: </p>
  
  <pre><code>:1A:CAD22021828,17
  :1B:RECEIVE GENERAL IND
  11 BEGUM ST 3-15A2
    VILL              AP IND 313 416
  :1C:/000061071257      CC
  RECEIVER GENERAL FOR IND
  C/O PNBB MAIN BRANCH
  11 BEGUM ST 3-15A2
  AA HYD         APIND
  </code></pre>
  
  <p>Now my requirement is to load this into 3 separate columns in target table as below:</p>
  
  <pre><code>1A    - CAD22021828,17
  1B    - RECEIVE GENERAL IND 
          11 BEGUM ST 3-15A2
          VILL              AP IND 313 416
  1C    - /000061071257      CC
          RECEIVER GENERAL FOR IND
          C/O PNBB MAIN BRANCH
          11 BEGUM ST 3-15A2
          AA HYD         APIND
  </code></pre>
  
  <p>can someone suggest how I can do this.</p>
  
  <p>This is oracle 11.2</p>
  
  <p>I have tried below code ;   </p>
  
  <pre><code>SELECT
      REGEXP_SUBSTR(mc_clob,':1A:([[:alnum:]]+\S+)') AS code1A,
      REGEXP_SUBSTR(mc_clob,':1B:([[:alnum:]]+\s+)') AS code1B,
      REGEXP_SUBSTR(mc_clob,':1C:([[:alnum:]]+\s+)') AS code1c
  FROM tableA;
  </code></pre>