I want to extract the name of a table participated in query from the query log. (The accesslog table is not good for me becuase it's only saved for 3 days and i need more than that).
I am trying to use regular expression, and im facing problem.
for example, valid queries are-
1.'SELECT * FROM dbname.tablename'
2.'SELECT * FROM dbname.tablename;'
3.'SELECT * FROM dbname.tablename where a=b'
So I want to get the string between the dbname, and ; or space, or nothing.
I am using regexp_substr, this is my query for expamle-
select distinct REGEXP_SUBSTR(upper('select * from dbname.tablename '),
'(?<=DBNAME.)(.*?)(?=[\s|;])', 1, 1)
but it doesn't consider the first case from my examples.
I know that this is the regexp that handles the first example(that nothing comes after the tablename in the query)
'(?<=DBNAME.)(.*?)(?!\S)', 1, 1)
I just dont know how to combine it with the origin regexp that i mentioned above (for the second and third example)
How can i write the regular exression to consider what ends with ;, ' ' without them (trunc those chars), or "nothing" but without truncing the last char?
Thanks a lot.
Slight variation of Fred's RedEx:
There's also an undocumented variation of RegExp_Substr which allows a 6th parameter indicating which matching group will be returned. This avoids lookahead/lookbehind
Thank you so much!
This is exactly what i was looking for.
But, for some DB names im facing with the error- SELECT FAILED 9134 Result exceeded the maximum length.
Do you have any solution to this problem?
Thanks a lot!
If you are using Dieter's first suggestion
that should support a result up to 16000 bytes - which is far larger than a valid SQL identifier. I guess we'd need to see some examples of data for which it is failing.
Thanks a lot, actually the problem was with the oreplace and not with regexp_substr..
Now i have one more question, is there any way to get 2 results from the regexp_substr?
for example if i have the query - ('select a.id from db_name.tablename1 a join db_name.tablename2 b on a.id=b.id')
is there any chance to get tablename1 and also tablename2?
Thank you so much guys for your help!