I need to use query in Google Sheet Spreadsheet to return text in Column B of a given length, and various other conditions. To make it easy, below is a simplified version concentrate solely on Len() function. Seems simple enough but unfortunately does not work.
=QUERY(Valuations!B1:B,"select B where LEN(B)>3 ")
I'm aware that SQL uses LEN(), where as LENGTH() for MySQL.
Are my syntax incorrect or it is not possible to check string length in Google Sheet Query?
You can do it using a filter
=filter(B:B,len(B:B)>=3)
And then if you want to combine that with other conditions, you can put it in a query e.g.
=query(filter(A:B,len(B:B)>=3),"select Col1,Col2 where Col1>1")
See this question
A regular expression can be used:
=QUERY(Valuations!B1:B, "select B where B matches '.{3,}'")
The regular expression explained:
. match any character
{3,} match the preceding symbol (the .) 3 or more times
You could also search for a specific length by modifying the expression to ^.{3}$
OR a range ^.{3,10}$
OR a maximum ^.{,10}$
^ the start of the string
$ the end of the sting
regex101.com is a valuable resource for regular expressions.
I am not associated with the site in any way but I use it all the time.
