1

Google Sheets queries cannot match text using contains or starts with when there the text to match contains a hyphen, and the hyphen is not the last character in the text to match. Am I doing something wrong? If not, is this a bug? Is there a workaround?

Example sheet.

"select count(A) where A starts with '2021-'" will return the count of matching results.

"select count(A) where A starts with '2021-0'", on the other hand, always claims there are no matching results even if there actually are.

3 Answers 3

1

The query() function happily matches text that contains hyphens.

When you use the contains operator, data in columns of type date gets automatically converted to text in the yyyy-m-d format, lacking leading zeros, regardless of their original format.

You can get what you ask for by converting the dates to text before feeding them to query() like this:

=arrayformula( query( trim(A2:A8), "select count(Col1) where Col1 starts with '2021-0' ", 0 ) )

But this is not a very good way to find dates before October, because it depends on the date format in the spreadsheet remaining yyyy-mm-dd. A better solution would not rely on the format, like this:

=query(A2:A8, "select count(A) where year(A) = 2021 and month(A)+1 < 10", 0)

Months are zero-indexed, hence the +1.

0

The values in Column A below the header (A1) are date values, not text values. If you want to use Google Query language text operators, among other ways to do get the required results, you might convert the date values to text values by using TO_TEXT Google Sheets built-in function

Instead of

=query(A2:A7,"select count(A) where A starts with '2021-0'",0)

you might use

=ArrayFormula(query(TO_TEXT(A2:A7),"select count(Col1) where Col1 starts with '2021-0'",0))

Please note that A was replaced by Col1 in the second parameter of query and the use of ArrayFormula

-1

It is NOT a bug.
It works as expected.

The issue is that dates are numbers while starts with and contains look for text.

You can see that in the following example where column B is an exact copy of column A but formatted as text.

enter image description here

Dates are scalar functions. You can read about them here
Also under the where clause it is mentioned

The where clause also supports some more complex string comparison operators. These operators take two strings as arguments; any non-string arguments (for example, dates or numbers) will be converted to strings before comparison.

So an expression like 2021- has no meaning as a number.

To look for the year use

=query(A2:A9,"select count(A) where A starts with year(date '2021-02-05')",0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.