User Documentation for School Database - Query Mode Syntax
Comparison Mode (Icon: <=>)
The first character(s) of the value indicate the match type
and if not one of the special characters then
= is assumed. All
comparisons are done using ASCII sort order.
- xyz means field content must exactly match "xyz"
- =xyz means same as above
- >xyz means field content must be greater than "xyz"
- <xyz means field content must be less than "xyz"
- >=xyz means field content must be greater than or equal to "xyz"
- <=xyz means field content must be less than or equal to "xyz"
- abc..xyz means field content must be greater than or equal to "abc" and less than or equal to "xyz"
Pattern Mode - Hook Left (Icon: <-) & Hook Right (Icon: ->)
This is a pattern match. Some characters have special meaning.
With
Hook Left enabled the pattern is anchored to the start of the field and a
* is added automatically to the end of the pattern,
hence matching content at the beginning of the field with anything at
the end. With
Hook Right enabled the pattern is anchored to the end of the field and a
* is added automatically to the beginning of the pattern,
hence matching content at the end of the field with anything at the beginning. With
neither enabled the pattern is floating and can match anywhere within the field. With both enabled the pattern is anchored to the start and the end
of the field and must match the whole content of the field.
Note that
Hook Left is the default template mode for any field in a query.
- * will match any number of characters
- ? will match any single character
- [] will match any character inside the bracket, case sensitive
- - when used inside [] defines a range of characters to match
Examples:
- xyz* is the default (Hook Left only enabled) and is actually rewritten as [Xx][Yy][Zz]* for a case insensitive match
- *[x]?[z]* matches "x" (not "X") followed by any single character followed by "z" (not "Z") anywhere in the field (equivalent to [x]?[z] with Hook Left and Hook Right off)
- 12[0-9] matches 120 to 129
- [A-C]* matches anything starting with "A", "B" or "C"
Multiple Patterns
In both
Comparison mode and
Pattern mode you can use
, (comma) to match on multiple values. For example:
- >24,8,0 in Comparison mode will match any value greater than "24" as well as the values "8" or "0"
- abc,xyz in Pattern mode will match either "abc" or "xyz" at the start of the field (Hook Left default)
Multi-row Queries
Normally a query template is only entered onto a single row and consists of multiple matches in each field which
are AND'ed together. However a query template can consist of multiple rows. Each row of a query template is OR'ed together. So
for example if we have a table with columns A, B and C then we could construct a query template like the following:
|
A |
B |
C |
ROW1 |
orange |
|
blue |
ROW2 |
|
red |
|
which would match all records where (column A is "orange" AND column C is
"blue") OR (column B is "red").
Filter Query
The
Use Template as Filter menu option allows you to save the current
query template as a
base filter after which everything else is AND'ed. For example, if we
toggled it on after setting up the query above, we could then construct a new
query template, for example:
Which would essentially match all records where ((column A is "orange" AND
column C is "blue") OR (column B is "red")) AND (column B is "white"),
ie. in this case only records where column A is "orange", column B is "white"
and column C is "blue".
The intention behind providing this mechanism was to allow the user to setup a complex base query which
they could then save and perform simpler queries on top of.
For example, on the
Person table create a query with
@ed.ac.uk in the
Email field and
Hook Left
turned off. You will get only those records with "@ed.ac.uk" somewhere in the
Email field.
Now toggle on
Use Template as Filter and then do the query again - you should see that the query
template has vanished yet if you click
Okay you still get the same reduced number of matching records.
You could now do a new query on the reduced set of records, eg. put
@sms in the
Email field which will
now be a query matching all records where the email contains an "@ed.ac.uk" address as well
as an "@sms" address. This is not necessarily easy to do any other way (although in this simpler case you
could use a single pattern match making sure to catch the two email address domains both ways around,
ie.
@sms*@ed.ac.uk,@ed.ac.uk*@sms).
--
TimColles - 17 Aug 2007