Menu
=QUERY(D4:F385;"select D,F where D contains '"&J4&"'")
If in J4 cell we have tree, this query grabs cells containing tree, but not Tree.
How to make it case insensitive?
504
column LIKE 'ABC' or column LIKE 'aBc' will return FALSE for such comparison. To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc' . In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default.
For your information, almost all of the functions in Google Sheets are case insensitive. But there are exceptions like QUERY, EXACT, and FIND functions, which are case sensitive.
When searching for partial strings in MySQL with LIKE you will match case-insensitive by default*. If you want to match case-sensitive, you can cast the value as binary and then do a byte-by-byte comparision vs. a character-by-character comparision. The only thing you need to add to your query is BINARY .
The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default. The logic of this query is perfectly reasonable but the execution plan is not: DB2.
The answer is unfortunately not complete, because now it will only work with "tree", but not with "Tree" or with "TREE". The correct solution would be to render always lower also the reference, as in:
=QUERY(D4:F385;"select D,F where LOWER(D) contains '"&lower(J4)&"'"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
