Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to trim substrings?

Re: How to trim substrings?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 18 Mar 2006 07:56:19 -0800
Message-ID: <1142697355.362761@yasure.drizzle.com>


Rauf Sarwar wrote:
> 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

Or possibly use translate.

Demo in Morgan's Library at www.psoug.org

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Sat Mar 18 2006 - 09:56:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US