Query does not filter from two values separated by | in NOT CONTAINS string

Viewed 76

I'm using the formula:

=ARRAYFORMULA(QUERY(TRIM(IMPORTXML("https://www.livescores.com/","//div[@class='content']//div[contains(@class,'row-gray')]")),"Where not Col1 contains 'Postp|Canc' "))

But for some reason 'Postp|Canc' not removing the lines that contain such values, what am I doing wrong?

enter image description here

2 Answers

| is a regex thing and in query only matches is regex attribute. use:

=ARRAYFORMULA(QUERY(TRIM(IMPORTXML("https://www.livescores.com/",
 "//div[@class='content']//div[contains(@class,'row-gray')]")),
 "where not Col1 matches '.*Postp.*|.*Canc.*'"))

or:

=ARRAYFORMULA(QUERY(TRIM(IMPORTXML("https://www.livescores.com/",
 "//div[@class='content']//div[contains(@class,'row-gray')]")),
 "where not Col1 contains 'Postp' 
     or not Col1 contains 'Canc'"))

In this answer, I would like to propose to modify the xpath. The modified xpath is as follows.

Modified xpath:

//div[@class='content']//div[div[@class='min'][not(contains(text(),'Postp') or contains(text(),'Canc'))]]

Modified formula:

=IMPORTXML("https://www.livescores.com/","//div[@class='content']//div[div[@class='min'][not(contains(text(),'Postp') or contains(text(),'Canc'))]]")

Result:

enter image description here

Reference:

Related