There are many ways with which you can filter your records
in Navicat's Table Window. You can also save your filters
for fast retrieval while you still have a table opened. Filters
can be saved to a profile so that you can retrieve them later
when you open a table again.
 Defining
filters for a table
Removing
filter conditions
Filtering
char, varchar, enum and set fields
Filtering
date/time fields
Filtering
numeric fields
Saving
and restoring your filter to and from a file
Export
filtered results by Export Wizard
Defining filters for a table
You can quickly filter your view of a table to show only
the records where a certain field equals some value.
In the Table Window, please click on "Filter Wizard"
button, then:
- Click the button "Click to add filter" to start
design your filters.
- After you added filters, please click "Click to aply
filters" to execute the filters.
The filtered result will be shown in the grid immediately.
You can choose to export it into other file formats by using
Export Wizard.
Removing filter conditions
Remove filter using the Filter Wizard
- Highlight the items in the Filter Wizard
- Right Click on an item in the Filter Wizard and select
Delete from the menu to delete the selected filter condition
Filtering char, varchar, enum and set fields
Filter operations
The table below show the filter operations you can choose
from and filter value options you will have to supply and
the resulting SQL that will be amended to the statement that
will be sent to the MySQL server.
| Filter |
Result |
| contains <?> |
My_Field LIKE '%your_value%' |
| begins with <?> |
My_Field LIKE 'your_value%' |
| ends with <?> |
My_Field LIKE '%your_value' |
| is (excactly) <?> |
My_Field = 'your_value' |
| doesn't contain <?> |
NOT (My_Field LIKE '%your_value%') |
| is empty |
My_Field IS NULL |
| is not empty |
My_Field IS NOT NULL |
| is greater than <?> |
My_Field > 'your_value' |
| is less than <?> |
My_Field < 'your_value' |
| is greater than or equal<?> |
My_Field >= 'your_value' |
| is less than or equal<?> |
My_Field <= 'your_value' |
| is blank or empty |
(My_Field = '' or My_Field IS NULL) |
| is not equal to <?> |
My_Field <> 'your_value' |
| is equal to<?> |
My_Field = 'your_value' |
Filtering date/time fields
Filter operations
The table below show the filter operations you can choose
from and filter value options you will have to supply and
the resulting SQL that will be amended to the statement that
will be sent to the MySQL server.
| Filter |
Result |
| between <?> and <?> |
((My_Field >= your_value) and (My_Field <= your_value)) |
| equals <?> |
My_Field = your_value |
| is empty |
My_Field IS NULL |
| is not empty |
My_Field IS NOT NULL |
| last 7 days |
((My_Field < today) and (My_Field >= today-7 days)) |
| last month |
((My_Field < first_of_this_month) and (My_Field >=
first_of_last_month)) |
| last week |
((My_Field < today) and (My_Field >= today-7 days)) |
| next 7 days |
((My_Field >= tomorrow) and (My_Field < tomorrow+7
days)) |
| next month |
((My_Field < first_of_this_month+2 months) and (My_Field
>= first_of_next_month)) |
| next week |
((My_Field < next_sunday+7 days) and (My_Field >=
next_sunday)) |
| not equal to |
My_Field <> your_value |
| on |
My_Field = your_value |
| on or after |
My_Field >= your_value |
| on or before |
My_Field <= your_value |
| this month |
((My_Field < first_day_next_month) and (My_Field
>= first_day_this_month)) |
| this week |
((My_Field < next_sunday) and (My_Field >= last_Sunday)) |
| today |
((My_Field >= today) and (My_Field < tomorrow)) |
| tomorrow |
((My_Field >= today+1 day) and (My_Field < today+2
days)) |
| yesterday |
((My_Field >= today-1 day) and (My_Field < today)) |
Filtering numeric fields
Filter operations
The table below show the filter operations you can choose
from and filter value options you will have to supply and
the resulting SQL that will be amended to the statement that
will be sent to the MySQL server.
| Filter |
Result |
| at least |
My_Field >= your_value |
| at most |
My_Field <= your_value |
| between |
((My_Field >= your_value) and (My_Field <= your_value)) |
| equals |
My_Field = your_value |
| is empty |
My_Field IS NULL |
| is not empty |
My_Field IS NOT NULL |
| less than |
My_Field < your_value |
| more than |
My_Field > your_value |
| not equal to |
My_Field <> your_value |
Saving and restoring your filter to and from a Profile
You can save and restore your filter conditions to a Profile.
Save your filter condition
Right Click in the Filter Wizard and select Save from the
menu. Enter the name of your filter profile and press Save.
Restore your filter condition
Right Click in the Filter Wizard and select Load from the
menu. In the Open dialog, locate your filter profile and press
Load to load your filter.
Export filtered results by Export Wizard
In the Table Window, please click on "Filter Wizard"
button, then:
- Click the button "Click to add filter" to start
design your filters.
- After you added filters, please click "Click to aply
filters" to execute the filters.
The filtered result will be shown in the grid immediately.
You can choose to export it into other file formats by using
Export Wizard.
|
