/
sql_clean.R
66 lines (57 loc) · 1.48 KB
/
sql_clean.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
#'
#' @title Clean and Read a SQL query
#'
#' @description
#' `r lifecycle::badge('stable')`
#'
#' A utility function to read in a SQL query from a character object, clipboard
#' or text file and remove all comments for use with database query packages
#'
#' @importFrom readr read_lines
#'
#' @param sql a SQL file or text string
#'
#' @examples
#' testSQL <- c(
#' "/********* INTRO HEADER COMMENTS",
#' "**********/",
#' " SELECT ",
#' " [VAR 1] -- with comments",
#' ",[VAR 2]",",[VAR 3]",
#' "FROM DATASET ","-- output here")
#' sql_clean(testSQL)
#'
#'
#' @return a cleaned SQL query without comments as a character string
#' @export
#'
sql_clean <- function(sql) {
## a character string
if(length(sql)==1){
## thats a SQL query stored in a .sql or .txt file
if(grepl(".(sql|txt)$",sql,ignore.case=TRUE)){
x <- readr::read_lines(sql)
} else {
## or just a SQL query as text eg. 'SELECT * FROM TABLE'
x <- sql
}
} else {
## or a list/vector of character strings that need to be put together
x <- sql
}
# note these gsubs could be combined with the next line separated with |
# remove comments
x <- gsub("--.*","",x)
# breaks, tabs and carriage returns; separated with | in regex
x <- gsub("\\r|\\t|\\n"," ",x)
# single string
x <- paste(x,collapse=" ")
# remove multiline /**/ comments
x <- gsub("/\\*(.|\n)*?\\*/","",x)
# whitespace cleanup
x <- trimws(x)
while(grepl(" ",x)){
x <- gsub(" "," ",x)
}
return(x)
}