Re: regular expression and v$sql question

From: Nigel Thomas <>
Date: Wed, 29 Jun 2011 17:03:38 +0100
Message-ID: <>

>How do I find the next word (not just the character, I want the tablename)
after a previous word. Some I don't care about spaces, >but I want to find the next word (table name in this case) after the word INTO?

For your first question - Have a look at Using Regular Expressions in * Oracle* Database<>eg select columns from v$sql ... where regexp_like(lower(sql_text), 'insert\s+into\s+tablename')

Get to know the character classes - \s+ stands in for any number of white space characters (it's a Perl character class - you could also use the equivalent Posix character class [:space:] - see

You can also match on word boundaries eg '\btablename\b' would match against your tablename but not against anothertablename. However be aware that words are alphanumeric so I think another_tablename would count as 2 words. So you may need a more complex regex that allows for the tablename to start after spaces and to be finished with a space, or an open bracket.

And of course it could be insert into schema.tablename and there are other variants; insert into ... select from, insert into .., (column list) values (...), insert into ... values (...) etc.

Hope that helps

Regards Nigel

Received on Wed Jun 29 2011 - 11:03:38 CDT

Original text of this message