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
• 10,290 points
33 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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,000 points

Related Questions In Data Analytics

0 votes
1 answer

Define a SQL query? What is the difference between SELECT and UPDATE Query? How do you use SQL in SAS?

Structured query language (SQL) is a programming ...READ MORE

answered Aug 24, 2018 in Data Analytics by ANMOL
• 3,620 points
35 views
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
651 views
0 votes
1 answer

Splitting the data into training and testing sets - R

You can use the sample.split() function from ...READ MORE

answered May 7, 2018 in Data Analytics by Bharani
• 4,550 points
471 views
0 votes
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,000 points
192 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,000 points
16 views
0 votes
1 answer

Error saying "Error in df$item : object of type 'closure' is not subsettable" when trying to use arules package

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

answered Nov 15, 2018 in Data Analytics by Maverick
• 10,000 points
52 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,000 points
249 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.