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,360 points
563 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,840 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
• 33,030 points
836 views
0 votes
1 answer
0 votes
1 answer
+1 vote
2 answers

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
74,159 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 9, 2018 in Data Analytics by Abhi
• 3,720 points
1,119 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,840 points
4,371 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,840 points
549 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,840 points
3,468 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP