R - select the text of the string between two values

Question

I have a string stored in R. What code can I use to extract text between two values? For instance:

sql <- "SELECT field1, field2, field3 FROM tbl WHERE x=y" 

I want to extract field1, field2, field3 as a separate line. I can use substring , but I don’t know how to determine the integer values ​​that refer to the end of "SELECT" and the beginning of "FROM".

Answer 1

Here is one option: gsub :

 gsub("^.*SELECT *(.*?) +FROM.*$", "\\1", sql) 

Answer 2

You can do it:

  split_sql = strsplit(sql, split = " ") result = paste(split_sql[1][[2]], split_sql[1][[3]], split_sql[1][[4]], sep = " ") 

This will give you:

  #[1] "field1, field2, field3" 

Answer 3

My preference would be:

 # Pull out strings between SELECT and FROM Fields = gsub("SELECT (.*) FROM.*","\\1",sql) # Remove spaces CleanFields = (gsub("\\s+", "", Fields)) # Split into items Items = unlist(strsplit(CleanFields,",")) 

Then you can index each field1 as Items [1]

(Of course, this can be entered into a function or combined into one line ...)

Answer 4

1) One way is to delete everything that you do not need:

 gsub("^SELECT *| FROM .*", "", sql) 

2) Another approach, if we had a data frame with required fields, is to execute the query using sqldf, and then take its names:

 library(sqldf) tbl <- data.frame(field1 = 1, field2 = 2, field3 = 3, x = 4, y = 5) toString(names(sqldf(sql)))