To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: [SPAM]-D] How to find broken UTF-8 characters ?
Date: 30 Apr 2010 10:21:03 GMT
Organization: Dis (not Dat) Organisation
Lines: 21
Message-ID: <hreauf$cma$1@reversiblemaps.ath.cx>
References: <4BD4DA7B.3000603@gmx.net> <4BD5670A.803@bankir.ru>
	<4BD58A00.7030905@gmx.net> <4BD835AD.2050902@magwerks.com>
	<4BD8F038.6060601@gmx.net>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-2 (Linux)

On 2010-04-29, Andreas <maps.on@gmx.net> wrote:
> Hi,
>
> while writing the reply below I found it sounds like beeing OT but it's 
> actually not.
> I just need a way to check if a collumn contains values that CAN NOT be 
> converted from Utf8 to Latin1.
> I tried:
> Select convert_to (my_column::text, 'LATIN1') from my_table;
>
> It raises an error that says translated:
> ERROR:  character 0xe28093 in encoding »UTF8« has no equivalent in »LATIN1«

use a regular expression.

 ISO8859-1 is easy, all the caracters a grouped together in unicode so
the regular expression consists of a single inverted range class

 SELECT pkey FROM tabname 
 WHERE ( textfield || textfiled2 || textfield3 ) ~ ('[^'||chr(1)||'-'||chr(255)||']');