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,430 points
55 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,040 points

Related Questions In Data Analytics

0 votes
1 answer
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
171 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
1,921 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,040 points
532 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,040 points
45 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,040 points
182 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,040 points
734 views