|
 |
|
Oracle Removing
Carriage Returns
Oracle Tips by Burleson Consulting |
Question:
I am trying to remove carriage returns at
the end of clob fields by using the following in SQL*Plus:
update scbdesc a
set a.scbdesc_text_narrative =
(select
substr(dbms_lob.substr(scbdesc_text_narrative),1,
length(scbdesc_text_narrative)
-1)
from scbdesc b
where
substr(dbms_lob.substr(b.scbdesc_text_narrative),
length(b.scbdesc_text_narrative),1)
= chr(10)
and a.scbdesc_subj_code =
b.scbdesc_subj_code
and a.scbdesc_crse_numb =
b.scbdesc_crse_numb
and a.scbdesc_term_code_eff =
b.scbdesc_term_code_eff
and a.scbdesc_term_code_end =
b.scbdesc_term_code_end)
where
substr(dbms_lob.substr(a.scbdesc_text_narrative),
length(a.scbdesc_text_narrative),1) =
chr(10)
This just nulls out the field in the table.
What do I need to change to make this work?
Thanks for your assistance!
This question posed on 02 February 2006
Answer:
Maybe just do a substitute
(replace)?
SQL> create table test(cola
clob);
SQL> insert into test values ('xxxyyy'||chr(10)||'zzzdddeee'||chr(10)||'ccccc')
SQL> /
SQL> commit;
SQL> select cola from test;
COLA
--------------------------------------------------------------------------xxxyyy
zzzdddeee
ccccc
SQL> select
replace(cola,chr(10),chr(32)) fixed from test;
FIXED
--------------------------------------------------------------------------
xxxyyy zzzdddeee ccccc
|
