From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: regexp_replace and UTF8 |
Date: | 2009-01-31 11:47:30 |
Message-ID: | gm1doi$914$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2009-01-30, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be> wrote:
>
> --=__Part8EA648F8.0__=
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
> I have a text field with data like this: 'de patiënt niet'
> (without the quotes).
> I would like to convert this string to look like this: 'de patiënt
> niet'
> Basically what I need to do (I think) is
> - get rid of the &, # and ;
> - convert the number to hex
> - make a UTF8 from that (thus: \xEB)
that is not UTF8.
the UTF8 representation for 'ë' in SQL is e'\xC3\xAB' or chr(235)
your input appears to be encoded in LATIN-1 (or possibly 8859-13)
I think you'll need to write a function.
here, where I have database encoding UTF8 this appears to work as you
desire.
CREATE or replace FUNCTION htmlent(inp text) returns text as
$f$
DECLARE
str text;
BEGIN
str= regexp_replace(quote_literal( inp)
,$$&#(\d+);$$
,$$'||chr(\1)||'$$
,'g');
execute 'select '||str into str;
return str;
END
$f$
LANGUAGE PLPGSQL;
select htmlent('de patiënt niet');
probably the above should be expanded to handle named entities
like '&' too.
characters outside of the LATIN-1 space are not handled
but aparently this is what you want.
From | Date | Subject | |
---|---|---|---|
Next Message | gherzig | 2009-02-01 01:13:27 | Re: dynamic OUT parameters? |
Previous Message | Craig Ringer | 2009-01-31 06:18:22 | Re: dynamic OUT parameters? |