Skip to content
Advertisement

How to extract the package name using regexp_substr

I’d like to extract the package name using regexp_substr.

For example:

create or replace package body something.pkg_test is

I want that:

pkg_test

I tried to use lookbehind just to ignore “create or replace package body something.”:

select regexp_substr('create or replace package body something.pkg_test is','((?<!createsorsreplacespackagesbodyssomething.).)*$',1,1) from dual;

But it doesn’t work. So I just need to extract the package name between “create or replace package body something” and the first space or n.

I need that because I want the package name before compile a .sql file and insert it in a table.

Advertisement

Answer

My guess is that, maybe you are trying to write some expression with positive look-behind:

(?<=createsorsreplacespackagesbodyssomething.)S*


Or maybe, alternatively, we’d use some expression without lookarounds, such as:

createsorsreplacespackagesbodyssomething.(S*).*

with capturing group 1, and our code might look like:

select regexp_replace('create or replace package body something.pkg_test is', 'createsorsreplacespackagesbodyssomething.(S*).*', '1') from dual;

not sure though.

Demo 2


Demo

The expression is explained on the top right panel of regex101.com, if you wish to explore/simplify/modify it, and in this link, you can watch how it would match against some sample inputs, if you like.


Reference

How to extract group from regular expression in Oracle?

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement