title: How to extract an undefined length of a string in Postgres
body: <p>I am really new at sql and I want to extract 'SWAMP RIVER NEAR DOVER PLAINS NY' from the following string:</p>
  
  <pre><code>&lt;a href='http://waterdata.usgs.gov/nwis/nwisman/?site_no=01199490'&gt;01199490&lt;/a&gt; SWAMP RIVER NEAR DOVER PLAINS NY&lt;/a&gt;
  </code></pre>
  
  <p>The problem is that length of the range I want to extract varies in each row.</p>
  
  <p>I tried the following:</p>
  
  <pre><code>select substring (name, 80 , char_length(name) - 4 ) from stream_gages; 
  </code></pre>
  
  <p>But I get: <code>SWAMP RIVER NEAR DOVER PLAINS NY&lt;/a&gt;</code> not matter what number I put after the minus sign.</p>
  
  <p>Is there a way I can do this?</p>
  
  <p>So, what I am exactly trying to do, is to extract the code and name of each stream gage stations into new columns, contained in:</p>
  
  <p><code>&lt;a href='http://waterdata.usgs.gov/nwis/nwisman/?site_no=01199490'&gt;01199490&lt;/a&gt; SWAMP RIVER NEAR DOVER PLAINS NY&lt;/a&gt;</code></p>
  
  <p>in order to do some GIS queries.</p>
  
  <p>The code is the number starting at position 57, and the name starting in postion 80 (in the case that the code length does not vary which is the most of the cases) however the length of name varies in each row. The length of the code also varies in a few row, but I can edit it manually if it is to hard to come up with a sql that does that. Howevery, for the name in all 240 rows the length is different. Thanks.</p>