Home » SQL & PL/SQL » SQL & PL/SQL » help with TRIM
help with TRIM [message #212844] Mon, 08 January 2007 09:12 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have the below snippet of code that I need to achieve a 'middle trim'. I want to 'trim' out the CHR(10) from the middle of the results. I'm getting results with CHR(10) between them, sometimes four or more lines...like the following. I want to get rid of the lines between east building and north building:



west building
east building




north building
south building





Is this possible?



LTRIM(RTRIM(MAX(RES2.RDP1)||CHR(10)||MAX(RES2.RDP2)||CHR(10)||MAX(RES2.RDP3)||CHR(10)||MAX(RES2.RDP4)||CHR(10)||MAX(RES2.RDP5),CHR(10)),CHR(10)) building


Thanks,
Stan
Re: help with TRIM [message #212846 is a reply to message #212844] Mon, 08 January 2007 09:14 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Use:

replace(<column_name>, chr(10))
Re: help with TRIM [message #212866 is a reply to message #212844] Mon, 08 January 2007 11:37 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Ebrian,

Am I somehow to incorporate your suggestion with the LTRIM & RTRIM or strictly use the REPLACE?

Thanks,
Stan
Re: help with TRIM [message #212879 is a reply to message #212866] Mon, 08 January 2007 13:22 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Strictly use REPLACE. It will replace all occurrences of the CHR(10) character.
Re: help with TRIM [message #212882 is a reply to message #212844] Mon, 08 January 2007 14:03 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I figured that's what you meant and that won't work. I'm putting the CHR(10) after each result so that each result is on a new line. But for some odd reason, I'm getting blank lines above, below, and in between the results. The LTRIM & RTRIM took care of the ones above and below but how do I get rid of the rest?
Using just the REPLACE giveS me the following result set:
west building east
building north
building south building


But I want the result set to look like this:
west building
east building
north building
south building


thanks,
Stan
Re: help with TRIM [message #212901 is a reply to message #212882] Mon, 08 January 2007 15:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Do you have some sample data that you'll be querying against?
Re: help with TRIM [message #212913 is a reply to message #212844] Mon, 08 January 2007 16:31 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Unfortunately no.

Thanks for looking though.

Stan
Previous Topic: Helpp!!
Next Topic: Ref Cursor - How to append records into ref cursor?
Goto Forum:
  


Current Time: Sat Dec 10 10:57:59 CST 2016

Total time taken to generate the page: 0.08386 seconds