title: PostgreSQL - Replace HTML Entities
body: <p>I have just set about the task of stripping out HTML entities from our database, as we do a lot of crawling and some of the crawlers didn't do this at input time :(</p>
  
  <p>So I started writing a bunch of queries that look like;</p>
  
  <pre><code>UPDATE nodes SET name=regexp_replace(name, '&amp;#xe0;', 'à', 'g') WHERE name LIKE '%#xe0%';
  UPDATE nodes SET name=regexp_replace(name, '&amp;#xe1;', 'á', 'g') WHERE name LIKE '%#xe1%';
  UPDATE nodes SET name=regexp_replace(name, '&amp;#xe2;', 'â', 'g') WHERE name LIKE '%#xe2%';
  </code></pre>
  
  <p>Which is clearly a pretty naive approach. I've been trying to figure out if there is something clever I can do with the decode function; maybe grabbing the html entity by regex like <code>/&amp;#x(..);/</code>, then passing <em>just</em> the <code>%1</code> part to the ascii decoder, and reconstructing the string...or something...</p>
  
  <p>Shall I just press on with the queries? There will probably only be 40 or so of them.</p>