From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: How to html-decode a html-encoded field
Date: 12 Apr 2012 12:26:18 GMT
Message-ID: <jm6hla$bld$1@reversiblemaps.ath.cx> (raw)
References: <CAAY=A7-PGBxQXJxVo2djnOKgwGd+0=bKjmG8_FHfOEFn1dSjbQ@mail.gmail.com>
On 2012-04-10, JORGE MALDONADO <jorgemal1960@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''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 ('magic'),('s&witch'),('toaster'))
select
column1 as input ,
(xpath('/z/text()', ('<z>'|| column1 ||'</z>')::xml))[1] as output
from sample
--
⚂⚃ 100% natural
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: How to html-decode a html-encoded field
In-Reply-To: <jm6hla$bld$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox