title: Select only numeric part of string only if it starts with a numeric value
body: <p>This is only giving me the last character (numeric) but I need the whole numeric string</p>
  
  <pre><code>SELECT substring('123 Main Street' FROM '%#"[0-9]#"%' FOR '#')
  </code></pre>
  
  <ul>
  <li>Results: 3</li>
  <li>Expecting: 123</li>
  </ul>
  
  <p>This gives me the same results but I need it to return a blank value:</p>
  
  <pre><code>SELECT substring('Main 123 Street' FROM '%#"[0-9]#"%' FOR '#')
  </code></pre>
  
  <ul>
  <li>Results: 3</li>
  <li>Expecting: </li>
  </ul>
  
  <p>NOTE: Postgres 7.4</p>
  
  <p>Helpful Link: <a href="http://www.postgresql.org/docs/7.4/static/functions-matching.html" rel="noreferrer">http://www.postgresql.org/docs/7.4/static/functions-matching.html</a></p>
  
  <p>UPDATE:</p>
  
  <pre><code>SELECT substring('Main 123 Street' FROM '[0-9]+')
  SELECT substring('123 Main Street' FROM '[0-9]+')
  </code></pre>
  
  <ul>
  <li>Both now return: 123</li>
  <li>Still need to skip or return full string of: 'Main 123 Street'</li>
  </ul>
  
  <p>UPDATE 2:</p>
  
  <p>Almost have it:</p>
  
  <p>This gives me the results I want if it doesn't start with a numeric value:</p>
  
  <pre><code>SELECT 
      COALESCE(substring('Main 123 Street' FROM '[0-9]*') || 'Main 123 Street', ''),
      substring('Main 123 Street' FROM '[0-9]*')
  </code></pre>
  
  <p>But this gives me both and I only want the second condition:</p>
  
  <pre><code>SELECT 
      COALESCE(substring('123 Main Street' FROM '[0-9]*') || '123 Main Street', ''),
      substring('123 Main Street' FROM '[0-9]*')
  </code></pre>
  
  <p>I GOT IT!!! Thanks for all who posted:</p>
  
  <pre><code>SELECT CASE
      WHEN COALESCE(substring(db_column FROM '[0-9]*'), db_column) != '' THEN COALESCE(substring(db_column FROM '[0-9]*'), db_column)
      ELSE db_column
  END AS addsress_string
  FROM db_table
  </code></pre>