Google-sheets – Google Spreadsheets Query( “where a matches ‘/[A-Z+]/’ ”) condition

google sheetsgoogle-sheets-queryregex

According to Google Query documentation:

matches – A (preg) regular expression match. haystack matches needle
is true if the regular expression in needle matches haystack.
Examples: where country matches '.*ia' matches India and Nigeria, but
not Indiana. Note that this is not a global search, so where country
matches 'an' will not match 'Canada'.

I tried to use it in the formula =query('Общий список'!A3:V;"select * where B matches '/[A-Z]+/' "), but the query returns empty output.

Reference: https://developers.google.com/chart/interactive/docs/querylanguage#Where

Best Answer

Note that this is not a global search...

This sentence explains what is going on: unlike the match in regexmatch, the matches clause of Query language requires the entire string to match the given regular expression. So, if you want to match strings where some part matches a regular expression re, the regular expression should be wrapped in .*: 

select * where A matches '.*re.*'
Related Topic