Remove quotation marks whitespaces from string-CodePudding

I'd like to remove quotation marks whitespace from string, in my case, change ' FIGUEIRAS040C ' by 'FIGUEIRAS040C':

my.ex<- "SELECT * FROM CF_2021 WHERE ID_UNIQUE = ' FIGUEIRAS040C '"

and output needs to be:

my.ex
[1] "SELECT * FROM CF_2021 WHERE ID_UNIQUE = 'FIGUEIRAS040C'"

I try to str_pad() function and regex expressions without success. Please, any help to solve it?

CodePudding user response：

1) Match a quote, any amount of spaces ( *), the shortest string (.*?) of any characters until another amount of spaces ( *) and a quote. Replace that with the part between the spaces and surround it with quotes. Use gsub instead of sub if there can be more than one. No packages are used.

sub("' *(.*?) *'", "'\\1'", my.ex)
## [1] "SELECT * FROM CF_2021 WHERE ID_UNIQUE = 'FIGUEIRAS040C'"

2) This approach works with SQLite and may work with whatever dialect of SQL you are using if it supports a trim function.

sub("('.*?')", "trim(\\1)", my.ex)
## [1] "SELECT * FROM CF_2021 WHERE ID_UNIQUE = trim(' FIGUEIRAS040C ')"

For example,

library(sqldf)

sql <- "select * from iris where Species = ' setosa ' limit 3"
sql2 <- sub("('.*?')", "trim(\\1)", sql)

sql2
## [1] "select * from iris where Species = trim(' setosa ') limit 3"

sqldf(sql2)
##   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1          5.1         3.5          1.4         0.2  setosa
## 2          4.9         3.0          1.4         0.2  setosa
## 3          4.7         3.2          1.3         0.2  setosa