Re: Backslash and LTrim/RTrim

From: Eugenio <eugenio.spadafora_nospam_at_compaq.com>
Date: 2000/07/14
Message-ID: <8kmo6f$qku$1@mailint03.im.hou.compaq.com>#1/1

Based on Sybrand answer, use a token that never appears in the text.

create or replace function trim_text(
  atext in varchar2
  ) return varchar2 as
  btext varchar2(100);
  c char(1) := chr(1);
begin

  btext := replace(atext, '\r\n', c);
  btext := rtrim(ltrim(btext, c), c);
  btext := replace(btext, c, '\r\n');

  return btext;
end;

select trim_text('\r\nChildren\r\nChildren\r\n\r\n') from dual;

or directly

select replace(

        rtrim(
         ltrim(
          replace('\r\nChildren\r\nChildren\r\n\r\n','\r\n', chr(1)),
          chr(1)),
         chr(1)),
        chr(1), '\r\n')

from dual;

--
Ciao

Eugenio
remove _nospam from reply address

Opinions are mine and do not necessarily reflect those of my company

=======================================================
Jonas Malmsten wrote in message <8kl5gg$u2g$1_at_nnrp1.deja.com>...


>I have a varchar2 field where the text is formatted in a on insert,

>update trigger. In the text a cr/lf is denoted by '\r\n'. I want to

>remove all '\r\n' from the beginning and the end of the text. Is there

>a way to do this using LTrim and RTrim? The first thought that came to

>my mind was to use this little snippet:

>

>create or replace function trim_text(

>  atext in varchar2

>  ) return varchar2 as

>  btext varchar2(100);

>begin

>  btext := ' ';

>  while atext ^= btext loop

>    btext := atext;

>    atext := rtrim(ltrim(atext,'\r\n'), '\r\n');

>  end loop;

>  return atext;

>end;

>

>It works as far as it removes all '\r\n' as I wanted to but it does

>more. It also removes all 'r' and all 'n' from then beginning and the

>end of the text, ie

>

>trim_text('\r\nChildren\r\n\r\n')

>

>will give me back 'Childre'

>

>I do have a workaround - but the above code looks much more nice:

>

>create or replace function trim_text(

>  atext in varchar2

>  ) return varchar2 as

>  btext varchar2(100);

>begin

>  btext := ' ';

>  while atext ^= btext loop

>    btext := atext;

>    if substr(atext, Length(atext) - 3, 4) = '\r\n' then

>      atext := substr(atext, 1, Length(atext) - 4);

>    end if;

>    if substr(atext, 1, 4) = '\r\n' then

>      atext := substr(atext, 5, Length(atext) - 4);

>    end if;

>  end loop;

>  return atext;

>end;

>

>//Jonas

>

>

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.

Received on Fri Jul 14 2000 - 00:00:00 CDT