Equivalent of SQL LIKE operator in R

Question

In an R script, I have a function that creates a data frame of files in a directory that have a specific extension.

The dataframe is always two columns with however many rows as there are files found with that specific extension.

The data frame ends up looking something like this:

|           Path           |   Filename  |
|:------------------------:|:-----------:|
| C:/Path/to/the/file1.ext |  file1.ext  |
| C:/Path/to/the/file2.ext |  file2.ext  |
| C:/Path/to/the/file3.ext |  file3.ext  |
| C:/Path/to/the/file4.ext |  file4.ext  |

Forgive the archaeic way that I express this question. I know that in SQL, you can apply where functions with like instead of =. So I could say `where Filename like '%1%' and it would pull out all files with a 1 in the name. Is there a way use something like this to set a variable in R?

I have a couple of different scripts that need to use the Filename pulled from this dataframe. The only reliable way I can think to tell the script which one to pull from is to set a variable like this.

Ultimately I would like these two (pseudo)expressions to yield the same thing.

x <- file1.ext

and

x like '%1%'

should both give x = file1.ext

Solution 1

you can use grepl() as in this answer

subset(a, grepl("1", a$filename))

Or if you're coming from an SQL background, you might want to look into sqldf

Solution 2

you can use like from data.table to get your sql like behaviour here. From the documentation see this example

library(data.table)
DT = data.table(Name=c("Mary","George","Martha"), Salary=c(2,3,4))
DT[Name %like% "^Mar"]

for your problem suppose you have a data.frame df like this

                                  path  filename
1:            C:/Path/to/the/file1.ext file1.ext
2:            C:/Path/to/the/file2.ext file2.ext
3:            C:/Path/to/the/file3.ext file3.ext
4:            C:/Path/to/the/file4.ext file4.ext

do

library(data.table)
DT<-as.data.table(df)
DT[filename %like% "1"]

should give

                       path  filename
1: C:/Path/to/the/file1.ext file1.ext

Comments

[JohnN]

This doesn't work. When I use paste0(subset(a, greql("1", a$filename))) it returns "character(0)" "character(0)"

[JohnN]

Okay, I had entered the column name incorrectly which is why it returned nothing. However, it is returning a table. It was supposed to only return the Filename. or the second column entry for that. Now at this point, this is easy to work with for what I want to do, but I though I'd point out that it doesn't do exactly what I was asking for.

[Willie D]

You can just add [,2] to the above formula.