I need to grab the schemaName.tableName from the string containing SQL query.

For example

Select t1.column1, t1.column2, t2.column3, to.column4 from ABC.TABLE1 t1 inner join XYZ.TABLE2 t2 where t1.id=t2.id;

After regex match I should get an array containing schemaName.tableName

[ABC.TABLE1,XYZ.TABLE2]

This pattern should be able to fetch schemaName.tableName from any SQL query.

How can I create this pattern please help me.

I tried following but didnt got result

https://regex101.com/r/D82EJh/1

[^\/,\s\?#] ?\.[^\/,\s] ?(?=\/|\s|$|\?|#)

CodePudding user response：

This pattern should be able to fetch schemaName.tableName from any SQL query.

To complete the task in this formulation, you will need to organize a whole project, the success of which will nevertheless depend on the chosen dialect of the sql language. Perhaps you will be satisfied with solving a simpler problem: how to get an array of words (including dots) that follow the words "from" or "join". Then I propose such a solution below.

const sql = "Select t1.column1, t1.column2, t2.column3, to.column4 from ABC.TABLE1 t1 inner join XYZ.TABLE2 t2 where t1.id=t2.id;";
console.log(sql);
const regex = /(?<=\b(from|join)\s)(\S )/g;
const found = sql.match(regex);
console.log(found);

This code should show in the console: ['ABC.TABLE1','XYZ.TABLE2']

CodePudding user response：

You could use a capture group, and match the allowed characters:

\b(?:join|from)\s (\w \.\w )

Explanation

• \b A word boundary
• (?:join|from)\s Match either join or from followed by 1 or more whitespace characters
• ( Capture group 1 (that holds the desired value)
  • \w \.\w Match 1 or more word characters, then a dot and again 1.or more word characters
• ) Close group 1

See a regex101 demo.

If there should be 1 dot and no more non whitespace characters after the word characters, you could assert a whitespace boundary:

\b(?:join|from)\s (\w \.\w )(?!\S)