Re: Lossy character conversion to Latin-1

[John DeSoi <desoi(at)pgedit(dot)com>]

On May 31, 2006, at 10:40 AM, John DeSoi wrote:

> Yes! Thanks very much -- I looked at that page several times and  
> missed regexp_replace.


Ok, now I know why I missed it. regexp_replace is only in PostgreSQL  
8.1 and later. I'm stuck with 8.0 for hosting at the moment.

I'm sure it is not very efficient, but the plpgsql function below  
does the same job for PostgreSQL versions prior to 8.1.

Thanks for the help,


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL



create or replace function lossy_latin(p_text text)
returns text as $$
declare
	t text;
	res text := '';
	ch text := substring(p_text from '[^\\u0000-\\u00FF]');
	pos integer;
begin
	if ch is null then
		return p_text;
	else
		t := p_text;
		loop
			pos := strpos(t, ch);
			res := res || substr(t, 1, pos - 1) || '?';
			t := substr(t, pos + 1);
			ch := substring(t from '[^\\u0000-\\u00FF]');
			if ch is null then
				res := res || t;
				exit;
			end if;
		end loop;
		return res;
	end if;
end;
$$ language plpgsql immutable;