regexp_substr help

Database
Fan

regexp_substr help

Hello, 

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.

Adi

6 REPLIES
Teradata Employee

Re: regexp_substr help

How about '(?<=DBNAME.)(.*?)(?=\W|$)'

Junior Contributor

Re: regexp_substr help

Slight variation of Fred's RedEx:

RegExp_Substr(x, '(?<=dbname\.)(.*?)(?=\W|$)',1,1,'i')

 

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

RegExp_Substr_gpl(x, '(dbname\.)(.*?)(\W|$)',1,1,'i',2)
Fan

Re: regexp_substr help

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!

Adi


Junior Contributor

Re: regexp_substr help

Simply cast the result of the RegExp_substr to a shorter VarChar.

Teradata Employee

Re: regexp_substr help

If you are using Dieter's first suggestion

RegExp_Substr(x, '(?<=dbname\.)(.*?)(?=\W|$)',1,1,'i')

 

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.

Fan

Re: regexp_substr help

Ok.

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!

Adi