Select table name and corresponding columns frim the sql file - R

Question

I have an sql file and I would like to select a row and its corresponding column items from the table. Is that possible?

Select Tab1.Name , Tab1.Age, Tab2.Dept_Name from emp Tab1 , department Tab2 where Tab1.Dept_No= Tab2.Dept_No

I would want the output in the following manner:

table-name	column-name
emp	Name
emp	Age
department	Dept_Name

Answer 1

Try something like this:

library(stringr)
library(dplyr)
library(tidyr)
text <- readLines("file.txt")
dataFrame <- data.frame(column-name = str_match(txt, "Select\\s+(.*?)\\s+from")[,2],
                 table-name  = str_match(txt, "from\\s+(.*?)\\s+where")[,2])

dataFrame <- dataFrame %>%
  separate_rows(column-name, sep = ",") %>%
  separate_rows(table-name, sep = ",") %>%
  filter(word(trimws(column-name), 1, sep = "\\.") == word(trimws(table-name), -1)) %>%
  mutate(column-name = word(trimws(column-name), -1, sep = "\\."),
         table-name  = word(trimws(table-name), 1))