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

0 votes

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

Dec 11, 2018 in Data Analytics by Ali
• 11,260 points
137 views

1 answer to this question.

0 votes

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))
answered Dec 11, 2018 by Maverick
• 10,820 points

Related Questions In Data Analytics

0 votes
1 answer

Shiny r ,I'm doing a dashboard and I can not replace in the table below the name of the column by choosing the selectinput.

Hi, When you want to change any input ...READ MORE

answered Aug 19, 2019 in Data Analytics by anonymous
• 32,490 points
105 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Which function can I use to clear the console in R and RStudio ?

Description                   Windows & Linux           Mac Clear console                      Ctrl+L ...READ MORE

answered Apr 17, 2018 in Data Analytics by anonymous
17,280 views
+10 votes
3 answers

Which is a better initiative to learn data science: Python or R?

Well it truly depends on your requirement, If ...READ MORE

answered Aug 8, 2018 in Data Analytics by Abhi
• 3,680 points
195 views
+1 vote
1 answer

Error saying "vector size cannot be NA" when using R with data mining

You can use the removesparseterm function.  Removes sparse ...READ MORE

answered Nov 15, 2018 in Data Analytics by Maverick
• 10,820 points
1,407 views
+1 vote
2 answers
0 votes
1 answer

Trying to find frequent itemsets of a data set using arules package

Try replacing ID <- c("A123","A123","A123","A123","B456","B456","B456") item <- c("bread", "butter", "milk", ...READ MORE

answered Nov 15, 2018 in Data Analytics by Maverick
• 10,820 points
108 views
0 votes
1 answer

Dynamically select element from a list and work with it - Shiny R

Follow these steps: Import the data into R Check ...READ MORE

answered Dec 5, 2018 in Data Analytics by Maverick
• 10,820 points
1,676 views