Re: regular expression and v$sql question
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: oracledbaquestions@xxxxxxxxx
- Date: Wed, 29 Jun 2011 17:03:38 +0100
>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<http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm>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
http://www.regular-expressions.info/posixbrackets.html).
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
Other related posts:
