Q
Problem solve Get help with specific problems with your technologies, process and projects.

Removing carriage returns at the end of clob fields

I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to make this work?

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!
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
This was last published in February 2006

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close