Google Spreadsheet Query Where Clause Condition Keywords

Have a look at the following screenshot of Sheet "Data":

Have a look at the following screenshot:

Formula in Cell:
A4: =query( 'Sheet Data'!A:Z ; "select A,B where A=4 or A=7 ")

A10: =query( 'Sheet Data'!A:Z ; "select A,B where A>=4 and A<=5 ")

A15: =query( 'Sheet Data'!A:Z ; "select A,B where A>8 and A<11 ")

A20: =query( 'Sheet Data'!A:Z ; "select A,B where A<>2 and A!=4 and not A>6 ")

D4: =query( 'Sheet Data'!A:Z ; "select A,B where B contains 'White' or B contains 'Black' ")

D13: =query( 'Sheet Data'!A:Z ; "select A,B where B matches 'White' or B matches 'Black' ")

D20: =query( 'Sheet Data'!A:Z ; "select A,B where B starts with 'White' or B starts with 'Black' ")

G4: =query( 'Sheet Data'!A:Z ; "select * where B starts with 'White' or B starts with 'Black' ")

G13: =query( 'Sheet Data'!A:Z ; "select * where B ends with 'White' or B ends with 'Black' ")

Cell G20: =query( 'Sheet Data'!A:Z ; "select * where B like '%White%' or B like '%Black%' ")

The where clause is used to return only rows that match a specified condition.

The simple comparison operators are <=, <, >, >=, =, !=, <>. Both comparison operators != <> mean not-equal. Strings are compared by lexicographic value. Note that equality is indicated by =, not == as in most computer languages. Comparing to null is done using is null or is not null.

You can join multiple conditions using the logical operators and, or, and not. Parentheses can be used to define explicit precedence.

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. String matching is case sensitive (you can use upper() or lower() scalar functions to work around that).

• contains - A substring match. whole contains part is true if part is anywhere within whole.
• starts with - A prefix match. value starts with prefix is true if prefix is at the beginning of value.
• ends with - A suffix match. value ends with suffix is true if suffix is at the end of value.
• matches - A (preg) regular expression match.
• like - A text search that supports two wildcards: %, which matches zero or more characters of any kind, and _ (underscore), which matches any one character. This is similar to the SQL LIKE operator.

I hope the above blog post will help you, and if you need more help then please do comment below, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive