61

I have a data.table with a character column, and want to select only those rows that contain a substring in it. Equivalent to SQL WHERE x LIKE '%substring%'

E.g.

> Months = data.table(Name = month.name, Number = 1:12)
> Months["mb" %in% Name]
Empty data.table (0 rows) of 2 cols: Name,Number

How would I select only the rows where Name contains "mb"?

Corvus
  • 7,548
  • 9
  • 42
  • 68

2 Answers2

103

data.table has a like function.

Months[like(Name,"mb")]
        Name Number
1: September      9
2:  November     11
3:  December     12

Or, %like% looks nicer :

> Months[Name %like% "mb"]
    Name Number
1: September      9
2:  November     11
3:  December     12

Note that %like% and like() use grepl (returns logical vector) rather than grep (returns integer locations). That's so it can be combined with other logical conditions :

> Months[Number<12 & Name %like% "mb"]
        Name Number
1: September      9
2:  November     11

and you get the power of regular expression search (not just % or * wildcard), too.

Matt Dowle
  • 58,872
  • 22
  • 166
  • 224
  • 1
    is there a way to use this command and update the table without `<-` , I was thinking in something like `Months[ Name== like(Name, "mb") ,] ` – rafa.pereira Sep 30 '15 at 08:59
  • @RafaelPereira Have you looked at `?data.table` (examples), read the documentation and taken the DataCamp course? `Months[like(Name,"mb"), someCol:=someValue]` – Matt Dowle Sep 30 '15 at 19:41
  • Thank you for the suggestions @Matt-Dowle. Maybe I wasn't clear enough. [I meant to ask you this](http://stackoverflow.com/questions/32882768/subset-data-table-without-using). – rafa.pereira Oct 01 '15 at 08:25
  • 1
    @RafaelPereira I see, yes. As Frank has now pointed to there, I'd like this feature too: http://stackoverflow.com/a/10791729/403310 – Matt Dowle Oct 01 '15 at 17:08
  • 2
    Can you use this to search for multiple strings? – user124123 Sep 23 '16 at 10:26
  • To negate it, use !like() – wordsforthewise Nov 01 '17 at 18:45
10

The operator %in% does not do partial string matching it is used for finding if values exist in another set of values i.e. "a" %in% c("a","b","c")

To do partial string matching you need to use the grep() function. You can use the grep to return an index of all columns with "mb" in it. Then subset the rows by that index

Months[grep("mb", Name)]    # data.table syntax slightly easier
Matt Dowle
  • 58,872
  • 22
  • 166
  • 224
LostLin
  • 7,762
  • 12
  • 51
  • 73