Re: [SQL] regexp_replace and UTF8
Thanks for the ideas!
The function Jasen suggests works partially.
There are also entities like
‘
’
“
”
…
The output of the htmlent function for these looks like |
Was that what you meant with "characters outside of the LATIN-1 space
are not handled
but apparently this is what you want." ?
Because in that case, they should be handled too.
How should that be done.
Thanks,
Bart
>>> Jasen Betts 2009-01-31 12:47 >>>
On 2009-01-30, Bart Degryse 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.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Function Returning a Set of Composite Value
Hi All ,
I have a function f_wrapper(seq,pat) which returns a composite value of type
(int,int[][]);
I am aware that I can run a query somthing like this to get it working
select * from f_wrapper('XYZ,'X') as m1(mid int,match int[][]);
but I want to pass another table column as parameter to function. - select
f_wrapper( t.seq,'X') from t_sequence t;
this return result which looks like -
(0,"{{0,2,2}}")
(1,"{{10,2,2}}")
when I try to parse this result in following way -
select f_wrappwe(t.seq,'X') frm t_sequence t as m1(mid int,match int[][]) ; I
get following syntax error
ERROR: syntax error at or near "int"
LINE 1: ...f_wrapper(seq,'MK') from nik_small_file as m1(mid int,match ...
^
any idea why I am getting this error or what is the alternative to parse
composite value in select column ?
my
purpose is to to show some column from table+(result from f_wrapper
function) but I am not able to parse f_wrapper result in required
column without calling f_wrapper twice like this -
select (f_wrapper(seq,'MK')).mid , (f_wrapper(seq,'MK')).match from
nik_small_file;
since my f_wrapper is going to be a heavy function so I can't afford to call it
twice.
any suggestion regarding this will be helpful.
Regards,
Nikhil
PS: Earlier I have sent same question to pgsql-novice as well but then I
realised that this might be more relevant on this list. sorry for posting it
twice.
Add more friends to your messenger and enjoy! Go to
http://messenger.yahoo.com/invite/
