SQL Server Full-Text Search (Enterprise)
Users of the Enterprise version of dbFront 1.1.3 have direct access to the Full-Text search functionality found in MySQL, Oracle and SQL Server.
SQL Server Full-Text searches can be run in four different modes: Default, Like (L:), Contains (C:) and FreeText (F:).
Full-Text search must be enabled at the database level and specialized indexes must be created in order for full-text search to work. See Creating Full-Text Indexes below for instructions.
Contains Full-Text Search
As soon as dbFront sees any advanced search operators then it will pass the query directly to SQL Server. You can also force dbFront to use the standard Contains search mode by preceding the search string with "C:"
The following is a subset of the Full-Text search functionality in SQL Server. For more details see the links below.
- C: : Starting a search with 'C:' will tell dbFront to use SQL Servers CONTAINS() instead of FREETEXT().
- AND, &: Specify that both words must be found. e.g. 'John AND Smith' returns all records having both "John" and "Smith".
- AND NOT, &!: Specify that the second word must NOT be found. e.g. 'John AND NOT Smith' returns all records having "John" and not "Smith".
- OR, |: Specify that both words must be found. e.g. 'John OR Smith' returns all records having either "John" or "Smith". The OR pattern can easily be used to return records that match multiple values, e.g. 'Red | Blue | Green | Yellow'.
- NEAR, ~: Specify that two words must be found near each other. e.g. 'John NEAR Smith' returns all records having "John" and "Smith" in the same field or document. An alternate syntax of 'NEAR((John, Smith), 3)' specifies that there can't be any more than three other words between "John" and "Smith".
- "prefix*": Return all rows containing words starting with a specified prefix. Double quotes are required. e.g ' "John*" AND Smith' will return all rows that contain "Smith" and some form of the name "John", such as "John", "Johnny", "Johnson", etc.
- (, ): Bracket parts of a search expression. e.g. '(Red | Blue | Green | Yellow) AND NOT Black'
For further details on the CONTAINS() syntax see: SQL Server Contains.
FreeText Full-Text Search
If the search text is prefixed with "F:", then dbFront will submit the query using SQL Server FREETEXT() instead of CONTAINS().
FREETEXT causes SQL Server to parse the search string and find inflectional forms, expansions and replacement forms of the words before searching. The intention is to get closer to the meaning of the original search while expanding to include related alternatives.
For further details on FREETEXT() see: SQL Server FreeText
Creating Full-Text Indexes
Creating Full-Text indexes in SQL Server requires the following steps and considerations:
- Create Table: Find or create the table that you are going to index.
- Create Unique Index: Find or create a unique index with a short key. If the primary key is too long (> 30 characters) you should consider creating a separate short Unique Index.
- Create FullText Catalog: Find or create a FullText catalog.
CREATE FULLTEXT CATALOG %CatalogName%; - Create FullText Index: Using the Unique Index name and FullText catalog name create the index using the following statement.
CREATE FULLTEXT INDEX
ON %TableName% (column1, column2, column3)
KEY INDEX %TableUniqueIndexName% ON %CatalogName%;
Troubleshooting
- Full-Text Search is not installed, or a full-text component cannot be loaded
This error means that you don't have the Full-Text components installed. If you are using SQL Express 2019 then you can download the SQL Express 2019 installer and update your installation to add the missing components.