X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4F61B474E4F for ; Sat, 22 Mar 2003 13:30:03 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id BBD16474E44 for ; Sat, 22 Mar 2003 13:29:58 -0500 (EST) Received: (qmail 22888 invoked from network); 22 Mar 2003 18:31:03 -0000 Received: from ns1.trade-india.com (HELO www.trade-india.com) (66.234.10.14) by infocom-236-129-del.trade-india.com with SMTP; 22 Mar 2003 18:31:03 -0000 Received: (qmail 20635 invoked by uid 99); 22 Mar 2003 18:30:35 -0000 Received: from 219.65.230.11 (SquirrelMail authenticated user mallah) by mail.trade-india.com with HTTP; Sun, 23 Mar 2003 00:00:35 +0530 (IST) Message-ID: <1156.219.65.230.11.1048357835.squirrel@mail.trade-india.com> Date: Sun, 23 Mar 2003 00:00:35 +0530 (IST) Subject: Re: newline character in SQL From: To: In-Reply-To: References: X-Priority: 3 Importance: Normal X-MSMail-Priority: Normal Cc: , X-Mailer: SquirrelMail (version 1.2.6) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-17.5 required=5.0 tests=IN_REP_TO,MISSING_MIMEOLE,MISSING_OUTLOOK_NAME, NO_REAL_NAME,QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50-cvs X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50-cvs (1.172-2003-02-14-exp) X-Archive-Number: 200303/530 X-Sequence-Number: 12569 is it \r\n instead of \n in that case try replace(comment , '\r\n' , '') regds mallah. > Mallah, > > The "replace" didn't work. The lines still showup on multiple lines. I li= ke to confirm if > Oracle (I am using 8.0.6) recognizes the '\n'? Any other ideas from anyo= ne? Appreciate the > help. > > Regards, > Tarun > > -----Original Message----- > From: Rajesh Kumar Mallah [mailto:mallah@trade-india.com] > Sent: Saturday, March 22, 2003 5:04 AM > To: Sethi Tarun-ETS017; pgsql-sql@postgresql.org > Subject: Re: [SQL] newline character in SQL > > > > > Use replace function as documented in > http://www.postgresql.org/docs/view.php?version=3D7.3&idoc=3D1&file=3Dfun= ctions-string.html > > eg in your case you can do: > Select name, replace(name, '\n' , '') as straight_name , comments , repl= ace(comments, '\n' , > '') as straight_comment from table where comment like '%Fine%'; > > > please not that replace function is inbuilt in postgresql 7.3.x , i prev= ious versions i am not > sure , but there is a work around for older versions. > > Regds > Mallah. > > > On Friday 21 Mar 2003 8:46 am, Sethi Tarun-ETS017 wrote: >> How can I remove the newline character from the value of a column. >> >> For Example: >> Select name, comments from table where comment like '%Fine%'; >> Results: >> NAME COMMENTS >> John M. This was a >> Fine piece of >> work. >> >> Above, I'd like the Results to appear: >> NAME COMMENTS >> John M. This was a Fine piece of work. >> >> I tried substr, instr, hedged right ... but can't get it to work. >> >> Any help will be greatly appreciated. >> >> Regards >> TS >> >> ---------------------------(end of broadcast)---------------------------= TIP 1: subscribe and >> unsubscribe commands go to majordomo@postgresql.org >> >> > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/