how to merge two lines into one in one field [message #424743] |
Mon, 05 October 2009 14:15  |
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 #424752 is a reply to message #424747] |
Mon, 05 October 2009 16:13   |
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
|
|
|
|