Thread: newline character in SQL
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
Use replace function as documented in http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-string.html eg in your case you can do: Select name, replace(name, '\n' , '') as straight_name , comments , replace(comments, '\n' , '') as straight_comment from table where comment like '%Fine%'; please not that replace function is inbuilt in postgresql 7.3.x , i previous 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.
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 like to confirm if > Oracle (I am using 8.0.6) recognizes the '\n'? Any other ideas from anyone? 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=7.3&idoc=1&file=functions-string.html > > eg in your case you can do: > Select name, replace(name, '\n' , '') as straight_name , comments , replace(comments, '\n' , > '') as straight_comment from table where comment like '%Fine%'; > > > please not that replace function is inbuilt in postgresql 7.3.x , i previous 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/
Mallah, The "replace" didn't work. The lines still showup on multiple lines. I like to confirm if Oracle (I am using 8.0.6) recognizesthe '\n'? Any other ideas from anyone? 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=7.3&idoc=1&file=functions-string.html eg in your case you can do: Select name, replace(name, '\n' , '') as straight_name , comments , replace(comments, '\n' , '') as straight_comment from table where comment like '%Fine%'; please not that replace function is inbuilt in postgresql 7.3.x , i previous 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.