Fuzzy Search

Fuzzy search capability over a TEXT index is possible only if the TEXT index is of type NGRAM. The GENERIC TEXT index cannot handle the fuzzy search.

Fuzzy searching can be used to search for misspellings or variations of a word. To do so, use the FUZZY operator followed by a string in double quotes to find an approximate match for the string.

Using the FUZZY operator is equivalent to breaking the string manually into substrings of length n and separating them with OR operators. For example, if you have a text index configured with the NGRAM term breaker and a MAXIMUM TERM LENGTH of 3, specifying 'FUZZY "500 main street" ' is equivalent to specifying '500 OR mai OR ain OR str OR tre OR ree OR eet'.

The FUZZY operator is useful in a full text search that returns a score. Many approximate matches may be returned, but usually only the matches with the highest scores are meaningful.

Note: Fuzzy search does not support prefix or suffix searching. For example, the search clause cannot be “v*” or “*vis”.

For more information on fuzzy search, see SQL Anywhere 11.0.1 > SQL Anywhere Server- SQL Usage > Querying and Modifying Data > Querying Data > Types of full text searches > Fuzzy searches.

Example 1: fuzzy search over an NGRAM TEXT index

Create a table and an NGRAM TEXT index:

CREATE TEXT CONFIGURATION NGRAMTxtcfg
   FROM default_char;
ALTER TEXT CONFIGURATION NGRAMTxtcfg TERM BREAKER    NGRAM;
ALTER TEXT CONFIGURATION NGRAMTxtcfg maximum term    length 3;
CREATE TABLE t_iq(a int, b varchar(100));
CREATE TEXT INDEX TXT_IQ on t_iq(b) CONFIGURATION    NGRAMTxtcfg

Insert this data into the table:

INSERT INTO t_iq values (1,'hello this is hira ');
INSERT INTO t_iq values(2, ' book he ookw worm okwo
kwor');
INSERT INTO t_iq values(3,'Michael is a good person');
INSERT INTO t_iq values(4,'hello this is evaa');
INSERT INTO t_iq values(5,'he is a bookworm');
INSERT INTO t_iq values (6,'boo ook okw kwo wor orm');

After inserting the data, execute this query to perform fuzzy searching over an NGRAM TEXT index:

SELECT * FROM t_iq WHERE CONTAINS (b,'FUZZY "bookerm"');

The results of the query are:

a         b       
2         book he ookw worm okwo kwor
5         he is a bookworm
6         boo ook okw kwo wor orm

Example 2: additional letter in the fuzzy search clause

This query illustrates an additional letter in the fuzzy search clause:

SELECT * FROM t_iq WHERE CONTAINS (b,'FUZZY "hellow"');

The results of the query are:

a         b       
1         hello this is hira
4         hello this is evaa

Example 3: letter removed from the fuzzy search clause

In this query, a letter is removed from the fuzzy search clause:

SELECT * FROM t_iq WHERE CONTAINS(b, 'FUZZY "hllo"');

The results of the query are:

a         b       
1         hello this is hira
4         hello this is evaa