Re: [SQL] newline character in SQL
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:[EMAIL PROTECTED] Sent: Saturday, March 22, 2003 5:04 AM To: Sethi Tarun-ETS017; [EMAIL PROTECTED] 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 [EMAIL PROTECTED] > > -- 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] FUNCTIONS PROBLEM
Hi Mario, I have used a record type to do this: create myfunc() returns record as ' declare return_val record; col1int; col2int; col3real; col4char; col1 := 5; col2 := 10; col3 := 2.7; col4 := ''z''; select col1,col2,col3,col4 into return_val; return return_val; end; ' language 'plpgsql'; When you call the function you need to specify the expected output: select * from myfunc() as (val1 int, val2 int, val3 real, val4 char); See the SELECT reference page in the documentation. There are other ways (which may be better) to do this that don't require the output types to be specified with the query but this is the one I got going first so I stuck with it. Hope this helps. Regards, David Witham Telephony Platforms Architect Unidial -Original Message- From: Mario Alberto Soto Cordones [mailto:[EMAIL PROTECTED] Sent: Friday, 21 March 2003 09:26 To: [EMAIL PROTECTED] Subject: [SQL] FUNCTIONS PROBLEM Importance: High Hi. i have a function and i need to return 4 fields but not work, any idea , please thank mario ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] FUNCTIONS PROBLEM
CREATE TYPE can be used as well i think in that case "as (val1 int, val2 int, val3 real, val4 char);" need not be done while selecting. regds mallah On Monday 24 Mar 2003 4:48 am, David Witham wrote: > Hi Mario, > > I have used a record type to do this: > > create myfunc() returns record as ' > > declare > return_val record; > col1int; > col2int; > col3real; > col4char; > > col1 := 5; > col2 := 10; > col3 := 2.7; > col4 := ''z''; > > select col1,col2,col3,col4 into return_val; > return return_val; > end; > ' language 'plpgsql'; > > When you call the function you need to specify the expected output: > > select * from myfunc() as (val1 int, val2 int, val3 real, val4 char); > > See the SELECT reference page in the documentation. > > There are other ways (which may be better) to do this that don't require > the output types to be specified with the query but this is the one I got > going first so I stuck with it. Hope this helps. > > Regards, > David Witham > Telephony Platforms Architect > Unidial > > -Original Message- > From: Mario Alberto Soto Cordones [mailto:[EMAIL PROTECTED] > Sent: Friday, 21 March 2003 09:26 > To: [EMAIL PROTECTED] > Subject: [SQL] FUNCTIONS PROBLEM > Importance: High > > > Hi. > > i have a function and i need to return 4 fields but not work, > > any idea , please > > thank > > mario > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
