## Sunday, August 11, 2013

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.