Re: Lossy character conversion to Latin-1

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;