Thread: Function to replace decimal value with character ?

Function to replace decimal value with character ?

From
Arnaud Lesauvage
Date:
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