Home » SQL & PL/SQL » SQL & PL/SQL » how to merge two lines into one in one field (Oracle 9i)
how to merge two lines into one in one field [message #424743] Mon, 05 October 2009 14:15 Go to next message
Bonita
Messages: 32
Registered: June 2008
Member
Oracle 9i.

Hi, guru,

I have one a table with 2 columns, both are varchar2 data type. However column B has two lines in it

Table X
( 
ID, VARCAHR2(20),
DESP, VARCHAR2 ( 200)
)

ID		DESP
0123		Description:
		This is testing


I want to merge DESP field into one line, looks like this

ID		DESP
0123		Description: This is testing


I'm assuming that there is '\r' between Description: and 'This is testing' , and tried to use function ltrim, rtime ,replace,...etc but all failed.

Can one of you help me out ? Thank you very much.
Re: how to merge two lines into one in one field [message #424745 is a reply to message #424743] Mon, 05 October 2009 14:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
REPLACE *should* work ...
SQL> create table x (id varchar2(20), desp varchar2(200));

Table created.

SQL> insert into x (id, desp) values
  2  ('0123', 'Description' || chr(10) || 'This is testing');

1 row created.

SQL> select * from x;

ID                   DESP
-------------------- --------------------------------------------------
0123                 Description
                     This is testing


SQL> select replace(desp, chr(10), '') result from x;

RESULT
--------------------------------------------------------------------------

DescriptionThis is testing

SQL>
Re: how to merge two lines into one in one field [message #424747 is a reply to message #424745] Mon, 05 October 2009 14:33 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
This is fantastic solution indeed.

Thank you VERY, VERY much !!! Laughing
Re: how to merge two lines into one in one field [message #424752 is a reply to message #424747] Mon, 05 October 2009 16:13 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
in general, new line can either be Windows style CR-LF or just LF (Unix style). So that means either chr(13)||chr(10) or just chr(10). The best solution is to try replace with (13)||(10) then just (10). You need to do it in the right order else you'l be stuck with some chr(13) by themselves. So basically...

replace(replace(desp, chr(13)||chr(10), ''), chr(10), '')


Do a decimal dump(desp, 1010) on your string to examine the bytes, or a simple instr(desp,chr(13)||chr(10)) to see what you have. Just because you find some that are chr(10), you may have others that are chr(13)||chr(10) depending on how they got in there...


[Updated on: Tue, 06 October 2009 10:55]

Report message to a moderator

Re: how to merge two lines into one in one field [message #424863 is a reply to message #424747] Tue, 06 October 2009 05:14 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You could also consider:

translate(desp, ' '||chr(10)||chr(13), ' ')
Previous Topic: double quotes in query (merged)
Next Topic: Getting duplicate records
Goto Forum:
  


Current Time: Sat Dec 10 09:04:39 CST 2016

Total time taken to generate the page: 0.52206 seconds