Re: How to trim substrings?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 16 Mar 2006 11:03:54 -0800
Message-ID: <1142535834.724226.168130@i39g2000cwa.googlegroups.com>

RK wrote:
> Help, please.
>
> I have to query a table to trim a certain field, and to catch its
> certain substrings. The tool I know is like ltrim() or rtrim(),
> something like
>
>
> select ltrim(ltrim(field_1,'a'),'b') from table_1;
>
> or
>
> select count(*) from table_1 where rtrim(field_1,'b') = 'xxc';
>
>
> I know ltrim() or rtrim() only trim out one character/digit once. How
> can I get all of the required results if I have more repeated 'a' to
> the left or 'b' to the right? Like
>
> aHaveOne
> aaHaveTwo
> abaabHaveMore
>
> ...
>
> This must be done purely by SQL*Plus query. Thanks.

Use only one ltrim for both,

SQL> select ltrim('abaabHaveMore', 'ab') from dual;

LTRIM('ABAABHAVEMORE','AB')

---

HaveMore

SQL> select ltrim('abbbaabbaabHaveMore', 'ab') from dual;

LTRIM('ABBBAABBAABHAVEMORE','A

---

HaveMore

Regards
/Rauf Received on Thu Mar 16 2006 - 13:03:54 CST