Skip to Main Content
  • Questions

  • How to find the chr(10) or chr(13) is in between a CLOB string

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 21, 2021 - 9:33 am UTC

Last updated: September 24, 2021 - 4:43 pm UTC

Version: PLSQL developer

Viewed 10K+ times! This question is

You Asked

I would need to replace the chr(10) or chr(13) from the string. However i would need to exclude those chr(10) or chr(13) in middle of the string.


eg:
(2S,4R)-1-{1-[(2-acetamido-2-deoxy-ß-D-
[(3-{5-[(2-acetamido-2-deoxy-ß-Dgalactopyranosyl)oxy]pentanamido}
5,11,18-trioxo-14-oxa-6,10,17-triazanonacosan-29-oyl}-4-hydroxypyrrolidin-2-
yl]methyl all-P-ambo-2'-O-methyl-P-thioguanylyl-(3'?5')-2'-O-methyl-Pthiouridylyl-(3'?5')-2'-O-methylcytidylyl-(3'?5')-2'-O-methyladenylyl-(3'

In the above example, i need not replace the chr(10) or chr(13), which is in between the string. Need to replace only at the end of the string. Please help to provide plsql script for this.

Thank you




and Connor said...

A simple RTRIM should do it.

eg small clob

SQL> create table t ( c clob );

Table created.

SQL>
SQL> insert into t values ('
  2  here are several lines
  3  of some stuff
  4  and there is a chr(10) at the end
  5  ');

1 row created.

SQL>
SQL> select c||'x' from t;

C||'X'
---------------------------------------------------

here are several lines
of some stuff
and there is a chr(10) at the end
x


SQL>
SQL> select rtrim(c,chr(10)||chr(13))||'x' from t;

RTRIM(C,CHR(10)||CHR(13))||'X'
---------------------------------------------------

here are several lines
of some stuff
and there is a chr(10) at the endx


eg large clob

SQL> create table t ( c clob );

Table created.

SQL>
SQL> declare
  2    x clob;
  3  begin
  4    insert into t values ( empty_clob() )
  5    returning c into x;
  6    for i in 1 .. 100 loop
  7      dbms_lob.writeappend(x,1000,rpad('z',1000,'z'));
  8    end loop;
  9    dbms_lob.writeappend(x,72,'
 10  here are several lines
 11  of some stuff
 12  and there is a chr(10) at the end
 13  ');
 14    commit;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> select length(c) from t;

 LENGTH(C)
----------
    100072

SQL> select rtrim(c,chr(10)||chr(13))||'x' from t;

RTRIM(C,CHR(10)||CHR(13))||'X'
--------------------------------------------------------------------------------
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
...
...
...
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

here are several lines
of some stuff
and there is a chr(10) at the endx


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 23, 2021 - 12:49 pm UTC

Thank you so much for your response with a detailed explanation with example.

I also need to replace the spaces in between the string, without removing the chr(10) or chr(13). If two spaces in between a string, need to replace with one space without replacing the chr(10) or chr(13). Need to provide warning if the string has chr(10) or chr(13) in between and has to replace 2 spaces to one.

A reader, September 23, 2021 - 12:53 pm UTC

Treid to use this script

trimclob(REGEXP_REPLACE(pcn_chemical_name, '(^[[:space:]]*|[[:space:]]*$)')

Hoever the 2 spaces in between the string are not replaced to one.

Thank you
Chris Saxon
September 24, 2021 - 4:43 pm UTC

[[:space:]] matches any whitespace character - including the linebreaks.

So that regex matches

The start of the string
The end of the string
Any number of spaces, linebreaks or other whitespace at the start of the string
Any number of spaces, linebreaks or other whitespace at the end of the string

I'm unclear exactly what you want to do. If you want to change turn two consecutive spaces into one, it's easiest to do:

replace ( x, '  ', ' ' )


That's REPLACE with two spaces, followed by one space.

Some example strings showing us what you want to change and how would help.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database