title: Regular expression to pad zeros
body: <p>I have a series of strings in my table that I want to order.
  eg.</p>
  
  <pre><code>Chapter 1 
  Chapter 2 
  Chapter 11 
  Chapter 12
  </code></pre>
  
  <p>To order them correctly I would need to pad them with zeros.</p>
  
  <pre><code>Chapter 001 
  Chapter 002 
  Chapter 011 
  Chapter 012
  </code></pre>
  
  <p>Maybe I can use <strong><code>regexp_replace()</code></strong> pad in zeros with the use of a regular expression.</p>
  
  <pre><code>  regexp_replace(chapters.name,'(\d+)\D*','0\1') as name
  </code></pre>
  
  <p>This solution does not work. I thought maybe I could figure out the length of the backreference and use that number to pad what I need:</p>
  
  <pre><code>  regexp_replace(chapters.name,'(\d+)\D*',lpad('\l',3-length('\1'),'0') as name
  </code></pre>
  
  <p>but a <code>\1</code> in <code>length()</code> always returns 2, even though I noticed using <code>\1</code> in certain postgres function translate <code>\1</code> to the actual captured data.</p>
  
  <p>How can I pad zeros using Postgres string functions and Postgres regex?</p>