CODEWITHSUNDEEP
sqlteradata
Neal Parker
Neal Parker

Reputation: 76

Extract table name using SQL from a column that stores queries

A table stores queries in it and I need to extract the tablename from those queries.

table:main

ID querytext
1 select a.record_id, a.name, b.person FROM database.atable a join database.btable b on b.id= a.id;
2 select c.record_id, c.name, d.person FROM database.ctable c join database.dtable d on c.id= d.id;

Expected results:

database.atable
database.ctable

SELECT SUBSTR(querytext, position('database.' in querytext), 30) FROM main;

This almost works, but I don't know how long or short the table name could be. If it is longer than 30, it will cut off results. If shorter, it could include other parts of the query. I was reading strtok and thought about using that to get to the space after the name to stop, but couldn't quite get it working.

Upvotes: 1

Views: 920

Answers (2)

access_granted
access_granted

Reputation: 1907

Little funky-looking, but should work:

select 
  strtok( substr( strsql, index(strsql,'FROM ')+length('FROM '), length(strsql)), ' ', 1 ) 
from (
select 
'select c.record_id, c.name, d.person FROM database.ctable c join database.dtable d on c.id= d.id' 
  as strsql
) s1;

or in your case

select strtok( 
  substr( querytext, index(querytext,'FROM ')+length('FROM '), length(querytext)) 
  ,' ', 1 
  ) from main;

Upvotes: 0

esqew
esqew

Reputation: 44701

Seems like this might be a good use case for REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(querytext, '(<?FROM database\.)(.+?\b)', 1, 1, 'i')

The pattern looks for the string literal FROM database. that immediately precedes a tablename matching the pattern (.+?\b).

Further - the pattern (.+?\b) matches any characters (.) occurring at least one time (+) non-greedily (? token) up to a word boundary character (\b), which judging by your sample data, should match on the whitespace immediately proceeding the table name.

You can see how this pattern is interpreted in even more detail using a utility like Regex101.

Further (albeit slightly tangential) reading regarding the PCRE-style RegExp support in Teradata: Regex syntax in teradata

Upvotes: 2

Related Questions