Introduction

When browsing a table a filter row consisting of one input field per column is shown between the table header and the actual table data. These filters allow you to perform quick filters in the currently selected table. The filter is performed as soon as you start typing (you can configure the delay time in Preferences).

Visual example

This section provides some examples for people not familiar with SQL.

Consider the following table:

Say, you want to filter for those records where Field2 contains ‘b’. In this case all you need to do is type ‘b’ in the column’s filter:

To filter for ‘b’ exactly, type ‘=b’.

Typing ‘%’ can be used as a wild card. ‘b%’ filters for records starting with ‘b’, ‘%b’ filters for records ending in ‘b’ and ‘%b%’ filters for records containing a ‘b’ at any position (this is the default filter option). You are free to use as many ‘%’ wild cards as you wish and in any combination. Here’s an example:

By default, we perform a ‘containing’ filter for numbers and a ‘containing’ (non case sensitive) filter for text. You can change the case sensitivity in the Pragmas tab (release 3.11 or later). You can override the default ‘containing’ search by specifying a comparison operator. You can do so by adding one of the supported operators at the beginning of your query. Have a look at this example where we filter for those records with a value greater than one in Field3:

For a full list of supported operators see the table below. You can also combine multiple filters by just using two or more input fields. By doing so a record must meet all criteria to be still shown:

Supported operators

There are no default comparison operators used. When filtering for both numbers and text, wild cards (‘%’) are allowed. The supported operators are:

Range Operators

NOTE - Range operators were added to DB4S on the 3.10 release.

When filtering for numbers, a range operator can be specified by using the ~ operator, for example, if filtering for 1 to 5, type ‘1~5’:

“Use in Filter Expression” helper options

For quick selection of the ‘equal to’ filter, you can click on any cell with the secondary mouse button and select the “Use as Exact Filter” option in order to get all the rows whose value in that column is the same as the chosen cell. Then you can modify the filter as you want. In the same context menu, you can select all the available filters through the “Use in Filter Expression” submenu using the current cell as value for the operator.

NOTE - Release 3.11

A look behind the scenes

For those familiar with the SQL syntax the filters are very easy to understand. The filters are translated into the WHERE part of the SELECT statement, joined by AND operators. The default comparison operator is LIKE but can be overridden as described in the table above. We automatically detect a numeric search and omit the quote characters around the filter term in this case while adding them when filtering for text. Single quote characters are automatically escaped and can therefore safely be used in a filter query. In case of any doubt you might want to check the generated SQL statement using the SQL Log panel - just make sure it is set to showing SQL submitted by the ‘Application’.