Function to replace decimal value with character ?

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: Pg general <pgsql-general(at)postgresql(dot)org>
Subject: Function to replace decimal value with character ?
Date: 2006-11-30 14:09:08
Message-ID: 456EE604.1010802@freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi List !

I have some data that comes from a HTTP server.

Some characters are encoded with the HTML convention :
&#<dec value in ISO8859-1>;

I'd like to replace these sequences with the 'real'
character, my database being encoded in UTF8.

I already found that to convert the decimal value to the
corresponding character, I had to use :

convert(chr(<decimal value>) using iso_8859_1_to_utf8)

Now, I'd like to convert an entire string at once, so I
thought of using regexp_replace, but this does not work :

select regexp_replace(mystring, '\&\#(...);',
convert(chr('\\1'::integer) using iso_8859_1_to_utf8), 'g')

Is there a way to accomplish it this way, or should I write
a more complicated plpgsql function ?

Thanks
--
Arnaud

Browse pgsql-general by date

  From Date Subject
Next Message Walter Vaughan 2006-11-30 14:09:44 Re: Stripping kernel FreeBSD - postgres
Previous Message Enrico 2006-11-30 13:50:41 Re: Stripping kernel FreeBSD - postgres