PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US

  • Overview
  • How to perform a "full-text" search?
  • How to create a full-text index?
  • Notes
  • Defining the stop words
  • Defining the synonyms
  • How to create a query used to perform a full-text search?
  • Full-text search: Query editor
  • Full-text search: Query in SQL code
  • Syntax of the search value
  • Specific case of words separated by a dash
  • Analyzing the result of a full-text query
  • Managing the full-text indexes by programming
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Overview
HFSQL proposes a quick search for character strings in the data. This search (called "full-text") is used to find a word or a set of words.
This allows you to index, without programming, the texts found in a HFSQL database. These texts can be found in "Text" items or in "Text memo" items.
An index can index one or more different items. The creation of the index can take the formatted texts into account (RTF, HTML): the tags found in these formats will be ignored during the indexing. This allows you to find words stored in RTF or HTML documents.
The results of the full-text indexing are proposed according to a relevance order ("ranking").
Caution: the "Full-text" search is not a simple search of "contains" type: the punctuation is not taken into account.
Versions 17 and later
WINDEV MobileiPhone/iPad This feature is now available for the iPhone/iPad applications.
New in version 17
WINDEV MobileiPhone/iPad This feature is now available for the iPhone/iPad applications.
WINDEV MobileiPhone/iPad This feature is now available for the iPhone/iPad applications.
Versions 18 and later
WINDEV MobileAndroid This feature is now available for the Android applications that are using HFSQL data.
WINDEVJava This feature is now available for the Java applications.
New in version 18
WINDEV MobileAndroid This feature is now available for the Android applications that are using HFSQL data.
WINDEVJava This feature is now available for the Java applications.
WINDEV MobileAndroid This feature is now available for the Android applications that are using HFSQL data.
WINDEVJava This feature is now available for the Java applications.
Note: From version 19, HFSQL is the new name of HyperFileSQL.
How to perform a "full-text" search?
To perform a "full-text" search, you must:
  1. Create a full-text index in the data files affected by this search.
  2. Create a query to perform the full-text search.
  3. Study and display the result of the query.
How to create a full-text index?
Creating a full-text index is performed in the data model editor.
To create a full-text index:
  1. In the data model editor, display the description of the items found in the relevant file ("Description of items" from the popup menu).
  2. Click Full-text index . The window for defining the full-text index is displayed. Select the items taking part in the composition of the full-text index. Only the "Text" items and the "Text memo" items found in the file description are proposed. Validate.
  3. The "Full-text index" item is displayed in the list of items.
  4. Select the item and modify its characteristics if necessary:
    • in the list of items, you have the ability to modify the name of the full-text index.
    • in the right section of the window, select the parameters of the full-text index:
      • minimum size of indexed words: only words longer than this size will be indexed. This size is set to 2 characters by default.
      • support for RTF tags: this option is used to specify whether the text contains RTF. In this case, the RTF tags are ignored during indexing.
      • support for HTML tags: this option is used to specify whether the text contains HTML. In this case, the HTML tags are ignored during indexing.
      • Versions 20 and later
        management of Unicode punctuation: this option is used to specify whether the text contains punctuation characters specific to Unicode (curved quotes, ...).
        New in version 20
        management of Unicode punctuation: this option is used to specify whether the text contains punctuation characters specific to Unicode (curved quotes, ...).
        management of Unicode punctuation: this option is used to specify whether the text contains punctuation characters specific to Unicode (curved quotes, ...).
      • sensitivity to the case: If the "Case sensitive" option is checked, the case of the words (uppercase/lowercase) will be taken into account during the index operation.
      • sensitivity to accented characters: If the "Accent sensitive" option is checked, the accented characters will be taken into account during the index operation. This option is required if the stemming is requested.
      • Versions 20 and later
        management of stop words: The management of stop words allows you to define the stop words that will be ignored during the index operation. In most cases, the stop words are the articles, the connecting words, ... The "Stop words" button is used to define the stop words that will be taken into account. See Defining the stop words for more details.
        New in version 20
        management of stop words: The management of stop words allows you to define the stop words that will be ignored during the index operation. In most cases, the stop words are the articles, the connecting words, ... The "Stop words" button is used to define the stop words that will be taken into account. See Defining the stop words for more details.
        management of stop words: The management of stop words allows you to define the stop words that will be ignored during the index operation. In most cases, the stop words are the articles, the connecting words, ... The "Stop words" button is used to define the stop words that will be taken into account. See Defining the stop words for more details.
      • Versions 20 and later
        management of synonyms: The management of synonyms is used to define the words that will be considered as synonyms. See Defining the synonyms for more details.
        New in version 20
        management of synonyms: The management of synonyms is used to define the words that will be considered as synonyms. See Defining the synonyms for more details.
        management of synonyms: The management of synonyms is used to define the words that will be considered as synonyms. See Defining the synonyms for more details.
      • Versions 20 and later
        management of stemming: If the management of synonyms is not enabled, you have the ability to enable the stemming. The stemming is used to perform searches on a full-text index while taking the variations of a given word into account. This feature is interesting for the knowledge databases and it allows you to find the words in singular or plural form. The stemming depends on the language used.
        If this option is enabled, select the language used.
        New in version 20
        management of stemming: If the management of synonyms is not enabled, you have the ability to enable the stemming. The stemming is used to perform searches on a full-text index while taking the variations of a given word into account. This feature is interesting for the knowledge databases and it allows you to find the words in singular or plural form. The stemming depends on the language used.
        If this option is enabled, select the language used.
        management of stemming: If the management of synonyms is not enabled, you have the ability to enable the stemming. The stemming is used to perform searches on a full-text index while taking the variations of a given word into account. This feature is interesting for the knowledge databases and it allows you to find the words in singular or plural form. The stemming depends on the language used.
        If this option is enabled, select the language used.
  5. Validate the description window of items.
  6. Save and generate the analysis. When generating the analysis, the description of the data files is modified and the full-text index is created (file with a ".FTX" extension).

Notes

  • To directly create a full-text index from a text item or from a text memo item, select the item in the description window of the items and click the link "Create a full-text index on the item".
  • WINDEVWEBDEV - Server codeiPhone/iPadWindows Mobile The full-text indexes can be managed in the data files described by programming: all you have to do is use a FullTextIndex Description to describe the index and HDescribeFullTextIndex to validate the index creation.
  • The composition of an existing full-text index can be modified at any time: to do so, click the "Edit the index" button found in the left section of the item description in the data model editor.
  • To create a Full-text Index item, a generation of the analysis and an automatic data modification must necessarily be performed.
  • Versions 20 and later
    Stemming: Because of grammatical exceptions (irregular verbs in English, verbs of third group in French, ...), some conjugations may not be grouped together (for example, a search for "fly" will not found the records containing "flown") or some plural forms may not be grouped with their singular form (foot/feet for example).
    New in version 20
    Stemming: Because of grammatical exceptions (irregular verbs in English, verbs of third group in French, ...), some conjugations may not be grouped together (for example, a search for "fly" will not found the records containing "flown") or some plural forms may not be grouped with their singular form (foot/feet for example).
    Stemming: Because of grammatical exceptions (irregular verbs in English, verbs of third group in French, ...), some conjugations may not be grouped together (for example, a search for "fly" will not found the records containing "flown") or some plural forms may not be grouped with their singular form (foot/feet for example).
Versions 20 and later

Defining the stop words

If the "Stop words" option is selected, all you have to do is click the "Edit the stop words" button to define the stop words.
  1. Click the "Edit the stop words" button.
  2. A list of stop words used by default is displayed.
  3. You can:
    • Add stop words: click the "Add" button. In the window that is displayed, enter the list of stop words to add. These words can be separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters.
    • Delete one or more stop words: select the stop words to delete from the list and click the "Delete" button.
    • Import stop words: click the "Import" button and select the text file containing the stop words. These words can be separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters.
    • Paste stop words: to paste the available stop words into the clipboard (and separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters), copy the words into the clipboard (CTRL C) and click the "Paste" button.
  4. Validate.
New in version 20

Defining the stop words

If the "Stop words" option is selected, all you have to do is click the "Edit the stop words" button to define the stop words.
  1. Click the "Edit the stop words" button.
  2. A list of stop words used by default is displayed.
  3. You can:
    • Add stop words: click the "Add" button. In the window that is displayed, enter the list of stop words to add. These words can be separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters.
    • Delete one or more stop words: select the stop words to delete from the list and click the "Delete" button.
    • Import stop words: click the "Import" button and select the text file containing the stop words. These words can be separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters.
    • Paste stop words: to paste the available stop words into the clipboard (and separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters), copy the words into the clipboard (CTRL C) and click the "Paste" button.
  4. Validate.

Defining the stop words

If the "Stop words" option is selected, all you have to do is click the "Edit the stop words" button to define the stop words.
  1. Click the "Edit the stop words" button.
  2. A list of stop words used by default is displayed.
  3. You can:
    • Add stop words: click the "Add" button. In the window that is displayed, enter the list of stop words to add. These words can be separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters.
    • Delete one or more stop words: select the stop words to delete from the list and click the "Delete" button.
    • Import stop words: click the "Import" button and select the text file containing the stop words. These words can be separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters.
    • Paste stop words: to paste the available stop words into the clipboard (and separated by spaces, tabulations, carriage returns or the ",", ";" and ":" characters), copy the words into the clipboard (CTRL C) and click the "Paste" button.
  4. Validate.
Versions 20 and later

Defining the synonyms

If the "Synonym" option is selected, all you have to do is click the "Edit the synonyms" button to define the synonyms.
  1. Click the "Edit the synonyms" button.
  2. In the window that is displayed, specify line by line the synonyms that will be taken into account. For example:
    house chalet villa condominium townhouse
  3. Validate.
New in version 20

Defining the synonyms

If the "Synonym" option is selected, all you have to do is click the "Edit the synonyms" button to define the synonyms.
  1. Click the "Edit the synonyms" button.
  2. In the window that is displayed, specify line by line the synonyms that will be taken into account. For example:
    house chalet villa condominium townhouse
  3. Validate.

Defining the synonyms

If the "Synonym" option is selected, all you have to do is click the "Edit the synonyms" button to define the synonyms.
  1. Click the "Edit the synonyms" button.
  2. In the window that is displayed, specify line by line the synonyms that will be taken into account. For example:
    house chalet villa condominium townhouse
  3. Validate.
How to create a query used to perform a full-text search?
The full-text searches are performed via queries: queries created in the query editor or queries created in SQL code. The search condition is entered when creating the query.

Full-text search: Query editor

To create a query performing a full-text search, all you have to do is add a "Full-text index" item to the query. The description window of a full-text search is automatically displayed.
This window is used to specify:
  • The search value. This value can be entered directly (a wizard is used to build the sought string) or it can correspond to a parameter. See "Syntax of the search value" for more details.
  • The sort options of the result.
When validating this window, the "full-text" item is automatically added into the query elements. This item corresponds to the relevance of the search result. A condition was automatically defined: only the records whose relevance is greater than 0 will be displayed in the query result.

Full-text search: Query in SQL code

To create a query that performs a full-text search in SQL code, use the following syntax:
MATCH (<Index>) AGAINST <Condition>
where:
  • <Index> corresponds to the list of items found in the index separated by commas (the order of the items is not important).
  • <Condition> corresponds to the sought string. See "Syntax of the search value" for more details.
Example: In this example, EDT_Find is an edit control and ConnectedUserID is a variable.
QRY_Find is Data Source


MyQuery is string = [
SELECT * FROM Contacts
WHERE MATCH(Contacts.LastName, Contacts.FirstName, Contacts.HTMLComment,
Contacts.RoughTextComment, Contacts.Comments,
Contacts.Phone, Contacts.Office,
Contacts.Cell, Contacts.Email, Contacts.MSN, Contacts.Internet_site,
Contacts.Country, Contacts.FaxNum, Contacts.City)
AGAINST ('
]
MyQuery = MyQuery + EDT_Find + [
')  
AND Contacts.UserID =  
]
MyQuery = MyQuery + ConnectedUserID + [
ORDER BY LastName DESC
]

HExecuteSQLQuery(QRY_SRCH, hQueryDefault, MyQuery)
FOR EACH QRY_SRCH
TableAddLine(TABLE_Contact_by_category, ...
QRY_SRCH.ContactID, QRY_SRCH.CategoryID, ConnectedUserID, ...
QRY_SRCH.LastName, QRY_SRCH.FirstName)
END
CASE ERROR:
Error(HErrorInfo())
Note: Query with parameter on a full-text index: how to ignore the parameter?
To ignore the parameter, the "MATCH" statement of the query must not be found in the query result but in the WHERE statement. Indeed, if the pertinence must be included in the result, the parameter must be specified to evaluate the result.
In order for a query created with the query editor to have the MATCH statement included in the WHERE statement, the pertinence must not be displayed in the result.
Example with relevance:
SELECT
MATCH(XX, YY, ZZ) AGAINST({ParamFullText}) AS PertinenceFullText
FROM
TABLE
WHERE
<Parameters>
AND
PertinenceFullText > 0
ORDER BY
PertinenceFullText DESC
Example without relevance:
SELECT *
FROM
TABLE
WHERE
<Parameters>
AND MATCH(XXX, YYY, ZZZ) AGAINST({ParamFullText}) > 0

Syntax of the search value

The search value can contain the following elements:
ElementMeaning
A single wordThe specified word will be sought. The relevance will be increased if the text contains this word.
Example: "WINDEV" searches for "WINDEV".
Two words separated by a space characterSearches for one of the words.
Example: "WINDEV WEBDEV" searches for the texts containing either "WINDEV" or "WEBDEV".
A word preceded by the "+" signThe specified word is mandatory.
Example: "+WINDEV" searches for the texts that necessarily contain "WINDEV".
A word preceded by the "-" signThe specified word must not be found in the text.
Example: "-Index" searches for the texts that do no contain "Index".
A word preceded by the "~" signIf the text contains the specified word, the relevance will be reduced.
One or more words enclosed in quotesThe specified words are searched in group and in order.
Caution: if "Ignore the words less than " differs from 0, the words enclosed in quotes less than the specified size will not be sought.
A word followed by the "*" signThe type of the search performed is "Starts with" the specified word.
Caution: The full-text search returns no result if the parameter passed is empty or null.

Specific case of words separated by a dash

If the text contains words separated by a dash ('-'), each section of the word is indexed independently in a full-text index.
Example:
  • The text contains "multi-platform": the two words "multi" and "platform" will be indexed independently.
  • During the search, the documents containing "multi" and/or "platform" will be found.
  • To find "multi-platform", the word must be enclosed between double quotes: "multi-platform".
    Caution: the search will return all the documents that contain "multi" and "platform" consecutively. The separator between the two words may not be the dash: it may be any separator (space, +, dot, comma, carriage return, tabulation, ...).
Analyzing the result of a full-text query
The result of a full-text query gives, for each record found in the data file, the relevance of the record in relation to the search value.
This relevance depends on several factors:
  • the number of times the sought word is found in the record.
  • the number of words in the record and their number of repetitions.
  • the ratio between the records that contain the sought words and the records that do not contain the sought words. Indeed, the more the sought word is found in all the records, the less the relevance will be important.
  • ...
Then, the result of a "full-text" search can be processed like any query result: you can for example display the result in a table, perform a sort according to the relevance, ...
Managing the full-text indexes by programming
Several WLanguage functions are used to manage the full-text indexes:
Versions 14 and later
HDescribeFullTextIndex
New in version 14
HDescribeFullTextIndex
HDescribeFullTextIndex
Describes a full-text index of data file created by programming.
Versions 14 and later
HListFullTextIndex
New in version 14
HListFullTextIndex
HListFullTextIndex
Returns the list of full-text indexes for a file (query or view) recognized by the HFSQL engine.
Versions 20 and later
HListStopWord
New in version 20
HListStopWord
HListStopWord
Returns the list of stop words used by a full-text index.
Versions 20 and later
HListSynonym
New in version 20
HListSynonym
HListSynonym
Returns the list of synonyms used by a full-text index.
Notes:
  • WINDEVWEBDEV - Server codeiPhone/iPadWindows Mobile Creating a full-text index in a data file created by programming can be done via a FullTextIndex Description variable.
  • HIndex is used to re-index the full-text indexes.
Minimum required version
  • Version 14
This page is also available for…
Comments
Click [Add] to post a comment