Re: [SQL] How to html-decode a html-encoded field

[Jasen Betts]

On 2012-04-10, JORGE MALDONADO <jorgemal1...@gmail.com> wrote:
> --000e0ce0d2ee43edb104bd553408
> Content-Type: text/plain; charset=ISO-8859-1
>
> I have a table with a varchar field, such a field is HTML ENCODED. So, for
> example, the string "PLAIN WHITE T'S" is saved as "PLAIN WHITE
> T&#39;&#39;S" (double quotes are not part of the string, I use them for
> clarity only). I need to perform a SELECT statement on this table and get
> the values HTML DECODED and I wonder if there is a function that I can
> include in such a statement for this purpose, for example "SELECT
> htmldecode(fld1) FROM table1". I will appreciate anu comments about my
> issue.

if you don't mind gettin an exception or the wrong answer on invalid 
input you can abuse xpath thusly:

with sample as ( values ('m&#97;gic'),('s&amp;witch'),('to&#x61;ster')) 
select 
  column1 as input ,
  (xpath('/z/text()', ('<z>'|| column1 ||'</z>')::xml))[1] as output 
from sample
      
-- 
⚂⚃ 100% natural


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql