What's the R equivalent of SQL's LIKE 'description%' statement?

Question

Not sure how else to ask this but, I want to search for a term within several string elements. Here's what my code looks like (but wrong):

inplay = vector(length=nrow(des))
for (ii in 1:nrow(des)) {
 if (des[ii] = 'In play%')
  inplay[ii] = 1
 else inplay[ii] = 0
}

des is a vector that stores strings such as "Swinging Strike", "In play (run(s))", "In play (out(s) recorded)" and etc. What I want inplay to store is a 1s and 0s vector corresponding with the des vector, with the 1s in inplay indicating that the des value had "In play%" in it and 0s otherwise.

I believe the 3rd line is incorrect, because all this does is return a vector of 0s with a 1 in the last element.

Thanks in advance!

Answer 1

The data.table package has syntax that is often similar to SQL. The package includes %like%, which is a "convenience function for calling regexpr". Here is an example taken from its help file:

## Create the data.table:
DT = data.table(Name=c("Mary","George","Martha"), Salary=c(2,3,4))

## Subset the DT table where the Name column is like "Mar%":
DT[Name %like% "^Mar"]
##      Name Salary
## 1:   Mary      2
## 2: Martha      4

Answer 2

The R analog to SQL's LIKE is just R's ordinary indexing syntax.

The 'LIKE' operator selects data rows from a table by matching string values in a specified column against a user-supplied pattern

> # create a data frame having a character column
> clrs = c("blue", "black", "brown", "beige", "berry", "bronze", "blue-green", "blueberry")
> dfx = data.frame(Velocity=sample(100, 8), Colors=clrs)
> dfx
            Velocity    Colors
        1       90       blue
        2       94      black
        3       71      brown
        4       36      beige
        5       75      berry
        6        2     bronze
        7       89    blue-green
        8       93    blueberry

> # create a pattern to use (the same as you would do when using the LIKE operator)
> ptn = '^be.*?'  # gets beige and berry but not blueberry
> # execute a pattern-matching function on your data to create an index vector
> ndx = grep(ptn, dfx$Colors, perl=T)
> # use this index vector to extract the rows you want from the data frome:
> selected_rows = dfx[ndx,]
> selected_rows
   Velocity Colors
     4       36  beige
     5       75  berry 

In SQL, that would be:

SELECT * FROM dfx WHERE Colors LIKE ptn3