title: Simulating regex capture groups in mysql
body: <p>As far as I can tell MySQL does not support retrieving the value of a capture group from a regex match. I have found a server side extensions (<a href="http://www.mysqludf.org/lib_mysqludf_preg/" rel="noreferrer">lib_mysqludf_preg</a>) which would add this functionality but I will not be able to install this extension in my environment.</p>
  
  <p>So, I'm looking for a way to simulate capturing a part of a regex match as a column in an SQL query.</p>
  
  <p>My data looks like the following (and I can't change the data format on the server):</p>
  
  <pre><code>+-----------------------------+
  | Version                     |
  +-----------------------------+
  | 1.2.3.4                     |
  | 10.20.30.40                 |
  | Obsidian-1.2.3.4            |
  | Obsidian-11.21.31.41        |
  | custom\Obsidian-11.21.31.41 |
  | custom\11.21.31.41          |
  +-----------------------------+
  </code></pre>
  
  <p>I'm looking to capture each of the last 4 digits from each row. The digits are always that last part of the value and they are always separated by dots. The following regex would match all of the values that I want:</p>
  
  <pre><code>.*[[:digit:]]+\\.[[:digit:]]+\\.[[:digit:]]+\\.[[:digit:]]+$
  </code></pre>
  
  <p>The result I'm hoping for is some combination of functions to capture each digit as a column so that I can use the digit in the where clause of my query as well as being able to get the version number back.</p>
  
  <pre><code>SELECT
      function1(...) as version1,
      function2(...) as version2,
      function3(...) as version3,
      function4(...) as version4
  FROM Version
  WHERE version1 &gt; 5;
  </code></pre>