How to exclude a specific string/pattern from a regular expression in oracle?

Question

I need an urgent help about an exclusion case in a regular expression (oracle).

The main regexp is that:

1([^4][:;]|[0-9][^:;].*)

I need to modify or enhance this regexp in order to exclude a spesific string "1013;" but could not achieve it. I have been searching a solution way for two days but could not find anything that works in oracle.

The most popular solution alternative (?!stringToExclude)Regexp is not working in oracle. (the version I have: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)

Do you have any idea about this issue? How can i overcome this problem?

my test sql statement checking the validation of new regexp is that:

select regexp_substr('1013;', '1([^34][:;]|[0-9][^:;].*)') from dual --> returns 1013;
select regexp_substr('10133;', '1([^34][:;]|[0-9][^:;].*)') from dual --> returns 10133;

select regexp_substr('1013;', 'to be regexp') from dual --> should return nothing
select regexp_substr('1013', 'to be regexp') from dual --> should return nothing
select regexp_substr('1013:', 'to be regexp') from dual --> should return nothing
select regexp_substr('10133;', 'to be regexp') from dual --> should return 10133;

Oracle's implementation of regular expression doesn't support look ahead as of yet (11gR2). — Vincent Malgrat
– Vincent Malgrat, Commented Aug 14, 2013 at 9:07

schurik · Accepted Answer · 2013-08-14 09:05:03Z

1

try to replace the string you want to exclude with a string that would not be found by your regexp. e.g

with str as 
(
select '1013;' as s from dual
union 
select '1013' from dual 
union 
select '1013:' from dual 
union 
select '10133;' from dual 
)
select 
  s
, regexp_substr(s, '1([^34][:;]|[0-9][^:;].*)')       --> regexp
, regexp_replace(s,'^1013($|:|;)','x')                --> replaced string
, regexp_substr(regexp_replace(s,'^1013($|:|;)','x')
                , '1([^34][:;]|[0-9][^:;].*)')        --> regexp with replaced string
from 
str
;

answered Aug 14, 2013 at 9:05

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mozbak Over a year ago

@schurik.... firstly thanks for your quick response.the regexp I have given was extracted from a configuration table so I could not replace the regexp with a sql statement. The thing I need to aim is to rewrite this regexp in order to exclude "1013['';:]". As Vincent declares, it could not be achieved by a lookahead structure. If so in your opinion, what kind of solution or regular expression will work?

Collectives™ on Stack Overflow

How to exclude a specific string/pattern from a regular expression in oracle?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related