FTS Indexes

SQL/R A.03.30 introduced support to use FTS indexes with SQL/R.

FTS indexes were added with Eloquence B.08.20 and allow full text ("keyword") searches in the database on a field or a group of fields. Please refer to the → Eloquence FTS documentation for more information.

The MATCHES operator may be used in the WHERE condition to access FTS indexes. The argument of the MATCHES operator uses FTS syntax, incuding wildcards, ranges or boolean expressions.

select custno,name1,zipcity from customers
where zipcity matches "wuppertal or berlin";

This will search the FTS index associated with the zipcity item for the word Wuppertal or Berlin. Please note that FTS searches are typically case insensitive.

CUSTNO NAME1                            ZIPCITY                                
16040  ROHRDÖRFER METALL GMBH           56OO WUPPERTAL 21/RONSDORF             
23058  SCHLITT, GEORG                   5600 Wuppertal-Langerfeld              
21058  Carl Schlösser GmbH & Co.        5600 Wuppertal-2                       
...
32043  WKW KUNSTSTOFFTECHNIK AG         1134 Berlin-Ost                        
26057  WLADASCH GmbH                    1000 Berlin 61                         
23074  WMU WERKZEUGMASCHINENH. GMBH     1000 Berlin 42                         
29057  WOCO, WOLF & CO, F. J.           1000 Berlin 30
...                         

This will search for records with the word "Berlin" but exclude any records with "Ost".

select count(*) from customers
where zipcity matches "berlin and not ost";

Multiple MATCHES terms may be combined with boolean operators. This is then reflected in the FTS search.

select custno,name from customers
where name1 matches "KEL@" 
and zipcity not matches "WUPPERTAL";

This performs the following FTS searches:

• search for "KEL@" on the FTS index associated with the item NAME1.
• the "and not Wuppertal" condition on the ZIPCITY item will remove any entries with Wuppertal from the previous results.

If the MATCHES operator is used but no applicable FTS index is available, the query will fail with an error.

ZIPCITY: Unable to resolve FTS field

The LIKE operator may be used in the WHERE condition to access FTS indexes under some conditions. If an applicable FTS index exists for the item and a leading and trailing wildcard is used, the FTS index will be searched. If a leading string is present and a trailing wildcard, then the FTS index is used if no btree index is available.

select custno,name1,zipcity from customers
where lcase(name1) like "%kel%";

The argument of the LIKE operator uses the std. SQL syntax. An FTS index may be used if a leading and trailing wildcard is present. The FTS dictionary is scanned first for any matching records and the results are then processed as usual. As FTS matches are not case sensitive the lcase function should be used to indicate a case insensitive comparison.

select custno,name from customers
where lcase(name1) like "kel%";

An FTS index may be used if a trailing wildcard is present and no btree index is available. The FTS dictionary is partially scanned for any matching records and the results are then processed as usual.