Oracle Removing Carriage Returns

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Removing Carriage Returns
Oracle Database Tips by Donald Burleson

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

��