Google Sheets – Query Language with Regex Matching

google sheetsregex

I have a query formula that looks into a table and pulls names for example:

   A                   B
1  john, andrew     blah
2  drew, mike       blah

I want it to show me all of the rows that involve drew, the query formula is select where A contains 'drew'. Both rows would show up, because andrew contains drew, but if I use the formula select where A = 'drew', nothing would show up because it is looking for an exact match. What do I do here? I was thinking maybe regular expression would help, but how would I do it?

Best Answer

To handle word boundaries, you need where A matches instead of where A contains. It allows the use of regular expressions such as .*\bdrew\b.*, meaning

  • containing the string "drew"
  • with word boundaries \b left and right of the string (i.e., the characters adjacent to it cannot be word characters)
  • with any other characters elsewhere in the string: .*

The last bullet item is needed because the logic of matches requires the entire content of A to match the given expression (unlike regexmatch function, which will attempt to match substrings).

So, in your case it would be

=query(A:B, "select * where A matches '.*\bdrew\b.*'")

or, if you want to also match "Drew" (case insensitive), then

=query(A:B, "select * where lower(A) matches '.*\bdrew\b.*'")
Related Topic