Using Regex to extract table names from a file containing SQL queries

Question

I have a text file containing a large number of requests. I want to get all the individual tables used in the whole file in all queries. The table name may appear after "from" or "join". How can I extract them by running a regex. Can anyone suggest a regex to get matches?

Answer 1

It depends on the structure of your file. Try using this:

(?<=from|join)(\s+\w+\b) 

Also enable the Multiline options if you did not split your file in an array or smth else with members of the same line of the line. Also try turning on the IgnorCase option.

Answer 2

I would use:

 r = new Regex("(from|join)\s+(?<table>\S+)", RegexOptions.IgnoreCase); 

when you have a match object "m", you will have a table name with

 m.Groups["table"].Value 

Example:

 string line = @"select * from tb_name join tb_name2 ON a=b WHERE x=y"; Regex r = new Regex(@"(from|join)\s+(?<table>\S+)", RegexOptions.IgnoreCase|RegexOptions.Compiled); Match m = r.Match(line); while (m.Success) { Console.WriteLine (m.Groups["table"].Value); m = m.NextMatch(); } 

it will print: tb_table tb_table2

Answer 3

Something like this might be:

 /(from|join)\s+(\w*\.)*(?<tablename>\w+)/ 

It will not match the names of escaped tables, but you need to make the regular expression evaluation case-insensitive.

Answer 4

 (from|join)\s(\w+)