Full-text search in databases

You can search for data in your database or in a group of databases without knowing the exact location of data.

The following video gives a short overview of full-text search functionality.

Search for data in databases, schemes, and separate tables

In the Database Explorer ( View | Tool Windows | Database Explorer) , select objects in which you want to perform your search. You can select a database or a group of databases, a scheme or schemes, or even separate tables.
Right-click the selection and select Tools | Full-Text Search. Alternatively, press Control+Alt+Shift+F.
In the search field of the Full-text Search dialog, type your search request and click Search.
In the Find tool window, review search results. To open a database object, double-click it. To return to the Find tool window (View | Tool Windows | Find), press Alt+3.

Full-text search options

Option	Description
Match case	Searches only for those instances that are written the same way as the query (preserving the case). A search for `Index` will return `Index`, `Indexes`, `Indexing` but not `index`.
Contains	Searches for a given combination of characters in words. For example, if you search for `ETTE`, you receive all `LIKE '%ETTE%'` results (`ANNETTE`, `JEANETTE`, `GILLETTE`, `BETTE`) from all the columns.
Starts with	Searches for a given combination of characters in the word beginning. For example, if you search for `JO`, you receive all `LIKE 'JO%'` results (`JOHNSON`, `JONES`, `JOYCE`, `JOAN`) from all the columns.
Ends with	Searches for a given combination of characters in the word beginning. For example, if you search for `TIN`, you receive all `LIKE '%TIN'` results (`MARTIN`, `AUSTIN`, `KRISTIN`, `JUSTIN`) from all the columns.
Matches	Searches for an exact combination of characters. For example, if you search for `BETTE`, you receive all `LIKE 'BETTE'` results `BETTE` from all the columns.
LIKE pattern	Searches for a combination of characters and SQL wildcards. For example, you can search for `a_%_%` and find any `LIKE 'a_%_%'` results that start with `a` and have at least 3 characters in length `ANDERSON`, `ALLEN`, `AMY`, `ANNA`.
Text columns	Searches only in columns that support the LIKE operation. For example, CHAR, VARCHAR, TINYTEXT, TEXT, and DATE (Oracle).
Only columns with full-text search indexes	Searches only in columns that have a created index. This feature is supported only for PostgreSQL, MySQL, and MariaDB. The query for PostgreSQL is `where col @@ plainto_tsquery('query')`. The query for MySQL and MariaDB is `where match(col) against ('query' in natural language mode)`.
Numeric columns	Searches only in columns with the numeric data type like INT, MEDIUMINT, SMALLINT, BIGINT and others.
All columns	Searches in all types of columns. For example, you can run this search to find a JSON element.
Show first N matches per table/view	Limits a number of found results for a table or a view.

Last modified: 07 October 2022