To perform full-text searching, use the Edit > Find (F3) command and, in the Find field of the displayed Find & Replace toolbar, enter the data pattern or plain substring to look for.
The Find Previous/Next commands simply scroll to the subsequent found table or form cell values. The Find All command performs full-text filtering of Text and Numeric fields (Long Text fields are not included).
Depending on which view is currently active (a table, form or a memo/object field), the searching procedure can concern:
- all numeric and text fields of all records contained by the (optionally filtered) table
- all numeric and text fields of the record displayed on the form
- the current memo field
There are three search modes that can be specified using the Options button menu:
Regular Expressions
The Find string is a data pattern based on standard regular expression syntax. Some examples of regular expressions:
| Pattern | Matches |
|---|---|
abc | substrings "abc" |
.bc | three-character substrings consisting of any character followed by "bc" |
\Aabc | field contents starting with "abc" |
abc\z | field contents ending with "abc" |
\Aabc.*123\z | field contents starting with "abc" and ending with "123", with any number of other characters in between |
abc\d\z | substrings ending with "abc" and one digit |
^a\d+ | field contents containing a line starting with "a" and at least one digit |
^a\d* | field contents containing a line starting with "a" and zero or more digits |
[ab]+c | substrings "abc", "aabc", "abbabc", etc., but not "c" |
[ab]*c | substrings "abc", "aabc", "abbabc", etc., and "c" |
[^ab]+c | substrings ending with "c" and not containing "a" or "b" |
\w\d{2,3} | substrings consisting of one letter followed by 2 or 3 digits |
\Aab\d{2,3}c | field contents beginning with "ab", two or three digits, and "c" |
abc|xyz | field contents containing "abc" or "xyz" - logical OR |
(?=.*abc)(?=.*xyz) | field contents containing "abc" and "xyz" - logical AND |
\A\z | empty fields |
For more information, see PCRE regular expression syntax summary.
The Replace string can contain:
(1) absolute references (by number) to capturing subpatterns, e.g. \1, \2...
A capturing subpattern (or a "group") is a part of the pattern enclosed in () parenthesis.
(2) \l, \L, \u, \U literals to (binary) switch upper- and lower-case conversion
(3) \r, \n - 'line feed' and 'new line' literals (by default, Alt+Enter and Ctrl+Enter insert the \n character into the text field)
(4) \s - a single space character
Note: to find and replace Unicode/non-ASCII characters, use Unicode regular expressions,
e.g. \p{L} instead of \w, \P{L} instead of \W, etc.
Examples:
| Find pattern: | cat |
| Replace string: | dog |
| Result: | replaces "cat" with "dog" |
| Find pattern: | \\ |
| Replace string: | / |
| Result: | replaces the "\" characters with "/" |
| Find pattern: | \A\z |
| Replace string: | NULL |
| Result: | fills empty fields with the "NULL" string |
| Find pattern: | \ANULL\z |
| Replace string: | (empty) |
| Result: | if a field contains only the "NULL" string, deletes its contents |
| Find pattern: | \b(\w+)(?:\W+\1\b)+ |
| Replace string: | \1 |
| Result: | removes duplicate words in fields |
| Find pattern: | (\b\w)(\w*(\W+|\z)) |
| Replace string: | \u\1\l\2 |
| Result: | converts first letters in words to uppercase, others to lowercase |
| Find pattern: | (\b\w)(\w*(\W+|\z)) |
| Replace string: | \1 |
| Result: | creates abbreviations consisting of first letters of words |
| Find pattern: | (\b\w(\d*))(\w*(\W+|\z)) |
| Replace string: | \1 |
| Result: | creates abbreviations consisting of first letters of words, leaves full numbers |
| Find pattern: | \b((\d{1,3}\.){3,3})\d{1,3}\b |
| Replace string: | \1\* |
| Result: | masks an IP address (e.g. 11.12.13.114 to 11.12.13.*) |
| Find pattern: | (\S*)(\s*)(\S*)(\s*)(\S*) |
| Replace string: | \g5\g4\g3\g2\g1 |
| Result: | reverses the order of up to the first 3 words in fields |
| Find pattern: | \R |
| Replace string: | \s |
| Result: | replaces line breaks with spaces |
| Find pattern: | ab+ |
| Database field content: | abcdefaabb |
| Replace string: | x |
| Replacement result: | xcdefax |
| Find pattern: | (.)a+\d{1,3} |
| Database field content: | abc aa0102 |
| Replace string: | \1 |
| Replacement result: | abc 2 |
| Find pattern: | (ab) |
| Database field content: | abcdef ghijk abb123 |
| Replace string: | \u\1\l\1xyz\s |
| Replacement result: | ABabxyz cdef ghijk ABabxyz b123 |
| Find pattern: | \R |
| Database field content: | abc |
| Replace string: | \s |
| Replacement result: | abc def ghi |
Plain Text - Partial Matching
The Find string represents a plain text string that is compared against the beginning of the field contents. The string can contain the wildcard "?" and "*" characters. Any non-wildcard "?" and "*" characters must be prefixed with a tilde (~).
Plain Text - Full Content Matching
The Find string represents a plain text string that is compared against the whole field content. The string can contain the wildcard "?" and "*" characters. Any non-wildcard "?" and "*" characters must be prefixed with a tilde (~).
If full-text searching is performed for a single memo field's data, the search mode automatically defaults to the Plain Text - Partial Matching search mode.
The Scripts button on the Find & Replace toolbar can be used to perform quick mass text replacing in a given table.
Note: the "search" and "replace" strings entered in the window below must be formatted like in a CSV text file, so text containing commas, line breaks or quoting symbols must be enclosed in "" and inner quoting symbols must be doubled.
