title: PostgreSQL, regex to match text fields with numeric values
body: <p>I have a column of type text, that contains random values. Some of them are numbers, some other texts, and some other mixed text and numbers. I'm trying to discriminate them as:</p>
  
  <pre><code>myfield   is_numeric
  ____________________
  -4        true
  0004      true
  4.00      true
  dog       false
  D04       false
  04f       false
  </code></pre>
  
  <p>Whereas others are names and other strings. I was using the regular expression</p>
  
  <pre><code>SELECT id,
         myfield 
         (myfield::varchar~ '^-?[0-9]*.?[0-9]*$') is_numeric 
  FROM mytable
  </code></pre>
  
  <p>To tell wether the row contains a valid number or not. However, I noticed that values like <code>D04</code> and <code>04c</code> return <code>true</code> for that regex, which, for my use case, is a false positive.</p>
  
  <p>Why is this happening? It seems that <code>^</code> is matching not necessarily the whole value, but any valid substring of the value. However, values like <code>D04f</code> do return <code>false</code>, so even if there's a numeric substring in the field, the combination of <code>^</code> and <code>$</code> operators is doing its job.</p>
  
  <p>I have temporarily resorted to using:</p>
  
  <pre><code>SELECT id, 
         myfield
         (myfield::varchar ~ '^-?[0-9]*.?[0-9]*$' 
         AND myfield::varchar !~ '[^0-9\-\.]') is_numeric 
  FROM mytable
  </code></pre>
  
  <p>But this seem inneficient (and does not rule out double dots), and I'm still wondering why the regex is correctly ruling out strings that start and end with a non-numeric character, while incorrecly returning true for string that contain only a trailing or leading non numeric character.</p>