Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Regular expression

babaraviApr 23 2012 — edited Apr 23 2012
Hi,

Please find the below detail. I want to implement the same logic using regular expression please advise. 
SQL>  with t as (
  2                          select 'AAABBB' col1 from dual union all
  3                         select 'AAACCC' from dual union all
  4                         select 'DDDDDD' from dual union all
  5                         select 'ZZZAAA' from dual union all
  6                         select 'KKKKKK' from dual union all
  7                         select 'MMMAAA' from dual
  8                        )
  9   SELECT * FROM t where substr(col1,1,3)<>'AAA' and substr(col1,-3)<>'AAA'
 10  /

COL1
------
DDDDDD
KKKKKK
db detail; 
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Regards
Ravikumar.A

Edited by: babaravi on Apr 23, 2012 2:41 PM
This post has been answered by Paul Horth on Apr 23 2012
Jump to Answer

Comments

TPD-Opitz 
regexp_like(col1.'AAA.*AAA')
bye
TPD
1 person found this helpful
Paul Horth
Answer
Try
with t as (
                            select 'AAABBB' col1 from dual union all
                            select 'AAACCC' from dual union all
                            select 'DDDDDD' from dual union all
                            select 'ZZZAAA' from dual union all
                            select 'KKKKKK' from dual union all
                            select 'MMMAAA' from dual
                           )
     SELECT * FROM t where not regexp_like(col1, '^AAA|AAA$');
Marked as Answer by babaravi · Sep 27 2020
Paul Horth
I suggest you try that out first: it doesn't work.
with t as (
                            select 'AAABBB' col1 from dual union all
                            select 'AAACCC' from dual union all
                            select 'DDDDDD' from dual union all
                            select 'ZZZAAA' from dual union all
                            select 'KKKKKK' from dual union all
                            select 'MMMAAA' from dual
                           )
     SELECT * FROM t where not regexp_like(col1, 'AAA.*AAA');

COL1
AAABBB
AAACCC
DDDDDD
ZZZAAA
KKKKKK
MMMAAA
908002 
 with t as (
                            select 'AAABBB' col1 from dual union all
                           select 'AAACCC' from dual union all
                           select 'DDDDDD' from dual union all
                           select 'ZZZAAA' from dual union all
                           select 'KKKKKK' from dual union all
                          select 'MMMAAA' from dual
                          )
    SELECT * FROM t where not REGEXP_LIKE( col1, '^AAA' )
    and not REGEXP_LIKE( col1, 'AAA$' );
Himanshu Binjola
h4. Below match string which start with "AAA" , ends with "AAA" and anything in between
with t as (
SELECT 'AAABBB' col1 FROM   dual UNION ALL
SELECT 'AAACCC'       FROM   dual UNION ALL
SELECT 'DDDDDD'       FROM   dual UNION ALL  
SELECT 'ZZZAAA'       FROM   dual UNION ALL
SELECT 'KKKKKK'        FROM   dual UNION ALL
SELECT 'MMMAAA'     FROM dual
)
SELECT * FROM t WHERE regexp_like(col1, '^AAA.*AAA$');

{code}
TPD-Opitz
Paul Horth wrote:
I suggest you try that out first: it doesn't work.
I overlooked th small word not (like some other guys too...)

bye
TPD
Paul Horth
Even then it wouldn't work - you may not have noticed but I put the NOT in for you in my example to you :-)

Edited by: Paul Horth on 23-Apr-2012 02:34
Solomon Yakobson
Paul Horth wrote:
you may not have noticed but I put the NOT in for you in my example to you :-)
I also noticed, your solution is not equivalent to OP's. Or's query selects rows that do not start with AAA and do not end with AAA. Your solution on top of such rows also returns sows that start with AAA but do not end with AAA and rows that end with AAA but do not start with AAA.

SY.
Paul Horth
Solomon,

Not with you. It doesn't.
with t as (
                            select 'AAABBB' col1 from dual union all
                            select 'AAACCC' from dual union all
                            select 'DDDDDD' from dual union all
                            select 'ZZZAAA' from dual union all
                            select 'KKKKKK' from dual union all
                            select 'MMMAAA' from dual
                           )
     SELECT * FROM t where not regexp_like(col1, '^AAA|AAA$');

COL1
DDDDDD
KKKKKK
It is not returning AAACCC for example.
Solomon Yakobson
Paul Horth wrote:

It doesn't.
Oops,

I misread your reply where you reference someone else's solution.

SY.
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 21 2012
Added on Apr 23 2012
10 comments
150 views