I can't figure out why my query with Matches does not work while it works fine with contains. I want to eventually pipe additional things into the match but need it to work first.
Contains: =QUERY(Raw!A2:P,"SELECT * WHERE K contains '<m>, 5/2/2020' ",0)
Matches: =QUERY(Raw!A2:P,"SELECT * WHERE K matches '.*<m>, 5/2/2020.*' ",0)
it semi-works when I remove/change the 5 but doesn't return all the matches... the sample data provided won't allow testing but I tested with 2,3,4,6 for example: =QUERY(Raw!A2:P,"SELECT * WHERE K matches '.*<m>, 3.*' ",0)
Sample data for one cell in column K:
Keith <k>, 5/2/2020: @Me: Thank you
_______________________
Keith <k>, 5/2/2020: @S: this is some text
_______________________
Mo <m>, 5/2/2020: @Me: more text is here
_______________________
ME <me>, 5/6/2020: text is here
_______________________
Jonathan <j>, 5/6/2020: @Me: Thank your
_______________________
Jonathan <j>, 5/6/2020: @S: text
Query's matches in Google sheets web app(unlike official mobile apps) doesn't seem to support regex flags like single line mode: (?s). Consider creating a issue with a link to this post in
Google sheets > Help > Help sheets improve.
Query function seems to run client side unlike other functions. If supported use, (?s) like:
=QUERY(Raw!A2:P,"SELECT * WHERE K matches '(?s).*<m>, 5/2/2020.*' ",0)
If not, simulate single line mode using (.|\n) (any character or new line):
=QUERY(Raw!A2:P,"SELECT * WHERE K matches '(.|\n)*<m>, 5/2/2020(.|\n)*' ",0)
Use contains instead. contains with OR should simulate matches' pipe | joining.
=QUERY(Raw!A2:P,"SELECT * WHERE K contains "&JOIN( " or K contains ","'<m>, 5/2/2020'" ,"'<k>'")&"",0)