title: Oracle SQL String split() Regex
body: <p>In my table Client, I have a column called "Address" with some fields like:</p>
  
  <pre><code>UNIT 12a Street name
  </code></pre>
  
  <p>How do I use Regex to split the stname and stnumber into 2 separated columns? There are thousand of different street names on my database. There are 3 posiblities in the strings:</p>
  
  <ol>
  <li>If the string is null, both new columns are null</li>
  <li><p>If only the street name exist (no number) then the street name will be moved to stname, and stnumber will be null.</p></li>
  <li><p>If the string does have number, sometimes it is like <em>Unit 12a ...</em> and sometimes it is just <em>21c ...</em>, in this case I want to split <em>Unit 12a</em> or <em>21c</em> into stnumber column, and leave the rest to stname column.</p></li>
  </ol>
  
  <p>I have already try this method:</p>
  
  <pre><code>select test
      --Group 1: Match everything up to the last digit, and one other character
      --Group 2: Everything after group 1
      ,regexp_replace(test, '(.*[[:digit:]].)(.*)', '\1') c1
      ,regexp_replace(test, '(.*[[:digit:]].)(.*)', '\2') c2
  from
  (
      select '1234john4345 this is a test.' test from dual union all
      select '1234john4345a this is a test' test from dual
  );
  </code></pre>
  
  <p>It does split it almost correctly, except for the part where the string contains no number, it duplicated the record into both column.</p>