Re: Backslash and LTrim/RTrim

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/07/13
Message-ID: <963520668.13257.0.pluto.d4ee154e@news.demon.nl>#1/1

Here ya go

Connected to:
Oracle8i Personal Edition Release 8.1.5.0.0 - Production With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> select replace(replace('\r\nChildren\r\n','\r'),'\n')   2 from dual;

REPLACE(

---

Children

SQL> Oracle is always more powerful than you think.

Hth,

Sybrand Bakker, Oracle DBA

"Jonas Malmsten" <jonas_at_malmsten.net> wrote in message news: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;
>
> file://Jonas
>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jul 13 2000 - 00:00:00 CDT