Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 277269FB7D4 for ; Wed, 8 Aug 2007 04:58:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 53374-04 for ; Wed, 8 Aug 2007 04:58:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id C2BA89FA5CE for ; Wed, 8 Aug 2007 04:58:19 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id l787vcnK078985 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 8 Aug 2007 01:57:46 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.14.1/8.14.1) with ESMTP id l787vbET046544; Wed, 8 Aug 2007 01:57:37 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.14.1/8.14.1/Submit) id l787vbOs046543; Wed, 8 Aug 2007 01:57:37 -0600 (MDT) (envelope-from mfuhr) Date: Wed, 8 Aug 2007 01:57:37 -0600 From: Michael Fuhr To: Chris Hoover Cc: "pgsql-admin@postgresql.org Admin" Subject: Re: Help with High value unicode characters Message-ID: <20070808075737.GA46023@winnie.fuhr.org> References: <1d219a6f0708071409s1f66c0f1u9ff69bc3cc580bcf@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1d219a6f0708071409s1f66c0f1u9ff69bc3cc580bcf@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/62 X-Sequence-Number: 26393 On Tue, Aug 07, 2007 at 05:09:35PM -0400, Chris Hoover wrote: > We need some help, we have some what we believe are high value unicode > characters (Unicode 0x2). What do you mean by "high value unicode characters (Unicode 0x2)"? Characters with code points in a plane other than Plane 0 (BMP, Basic Multilingual Plane), i.e., with a code point greater than U+FFFF? > How can you search and replace for these? We are storing this data > in a text field, and having the data contain this unicode value is > violating our xml rules the application uses and causing abends in > our application. If I understand what you're asking then you should be able to use regexp_replace (8.1 and later) to fix the data. Example: UPDATE tablename SET columnname = regexp_replace(columnname, E'[\\U00010000-\\U0010FFFF]+', '', 'g') WHERE columnname ~ E'[\\U00010000-\\U0010FFFF]'; If that doesn't help then please clarify the problem. -- Michael Fuhr