X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3E1C3D1EF56 for ; Mon, 3 May 2004 17:56:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38518-08 for ; Mon, 3 May 2004 17:56:02 -0300 (ADT) Received: from mail.acadaca.com (mail18.datapipe.com [64.27.65.37]) by svr1.postgresql.org (Postfix) with ESMTP id 15C4AD1EFA0 for ; Mon, 3 May 2004 17:55:56 -0300 (ADT) Received: from Pedro [66.9.138.34] by mail.acadaca.com with ESMTP (SMTPD32-8.05) id A1E21E780026; Mon, 03 May 2004 16:56:02 -0400 From: "Noah Davis" To: Subject: Re: Bad character data Date: Mon, 3 May 2004 16:55:08 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Thread-Index: AcQxTppZoNuqRw2UQR6s77IYF1VoJwAAMJ4w In-Reply-To: Message-Id: <200405031656345.SM01600@Pedro> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/24 X-Sequence-Number: 13340 Hi Doug, Thanks for the response. I do realize it's not the ideal situation, but it's the database I inherited, so not much I can do there :) . I tried the to_ascii and it doesn't seem to help much. It may be that I can try a different client and get more legible characters. Not sure. -----Original Message----- From: douglas morrison [mailto:luckycat@comcast.net] Sent: Monday, May 03, 2004 4:38 PM To: Noah Davis Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Bad character data The lack of responses is prolly because this sort of thing is usually handled by the client... The client for input should be stripping/converting to ASCII/unicode whichever chars are unwanted and notifying the user if anything is removed/unusable. The client for display should then be able to parse the chars correctly... You might be able to use your current data if you change your SELECT to something like: SELECT to_ascii(columnName, 'LATIN1') AS convertedColumn FROM tableName; hth, doug On May 3, 2004, at 3:23 PM, Noah Davis wrote: > I posted this to the pgsql general list, but alas, I did not get any > responses. Perhaps someone here could be of assistance? > > I have a database with some bad characters in it -- some users had > entered MS Word smart quotes, em dashes, foreign characters, and they > look like gibberish coming out of the database. Most important are the > smart quotes I guess. > > What's the best way to replace these characters? I thought I might be > able to run a simple SQL UPDATE command, but some of the gibberish for > different characters looks the same (at least from my client it does), > and it would clobber them all. > > I have a feeling there's some sort of ASCII code or unicode solution > to this problem, but I could use am little push in the right > direction. > > Thanks. > > Noah. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >