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:

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