Introduction
New functionality in Mimer SQL version 11 makes it easier than ever to do word search combined with autocomplete.
Many autocomplete implementations depend on a separate dictionary which is used to provide the suggested words. This dictionary can be distributed with the application or operating system, and needs to be maintained to include new words as they are entered by the users.
With Mimer SQL 11 there’s no need for a separate dictionary. The words are already in your table, and a word search index will give you instant access to new words as they are inserted.
We will here show how Mimer SQL can be used to provide word search combined with autocomplete.
The autocomplete case
First of all, create a test table and insert some data. (Use an _1 collation to get a case and accent insensitive search, for example eor_1 which suits many European languages.)
create table wstable ( id int primary key, comment nvarchar(50) collate eor_1); insert into wstable values (1, 'I love pancakes'); insert into wstable values (2, 'What is a partial lunar eclipse?'); insert into wstable values (3, 'North Side Story'); insert into wstable values (4, 'Polar bear tango'); insert into wstable values (5, 'Pancake party in Paris'); insert into wstable values (6, 'Sunday surfing'); insert into wstable values (7, 'Camping at the North Pole'); insert into wstable values (8, 'Sunday Matinee show');
Create a word search index on the comment column:
create index ix_wstable_comments_ws on wstable (comment for word_search);
You can now select directly from the index, to see its contents:
SQL>select * from ix_wstable_comments_ws;
comment id
================================================== ===========
a 2
at 7
bear 4
Camping 7
eclipse 2
I 1
in 5
is 2
love 1
lunar 2
Matinee 8
North 3
North 7
Pancake 5
pancakes 1
Paris 5
partial 2
party 5
Polar 4
Pole 7
show 8
Side 3
Story 3
Sunday 6
Sunday 8
surfing 6
tango 4
the 7
What 2
29 rows found
Usually you create an index to significantly improve performance, which of course is the case here too. But a word search index will also be very useful when implementing autocomplete, as it also works as the dictionary – a dictionary which is automatically updated when users add new comments to your table.
Use SELECT DISTINCT to list the different words found in the comments column:
SQL>select distinct comment from ix_wstable_comments_ws;
comment
==================================================
a
at
bear
Camping
eclipse
I
in
is
love
lunar
Matinee
North
Pancake
pancakes
Paris
partial
party
Polar
Pole
show
Side
Story
Sunday
surfing
tango
the
What
27 rows found
To implement autocomplete, use the begins() function to step by step reduce the number of suggested words, for each character the user is typing.
For example, if the user first enters the character ‘p’, use it as argument to the begins() function when searching the index to give a list of words to suggest:
SQL>select distinct comment from ix_wstable_comments_ws
SQL&where begins(comment, 'p')
SQL&fetch first 10 rows only;
comment
==================================================
Pancake
pancakes
Paris
partial
party
Polar
Pole
7 rows found
If the user then types ‘a’, add that character to the begins() search:
SQL>select distinct comment from ix_wstable_comments_ws
SQL&where begins(comment, 'pa')
SQL&fetch first 10 rows only;
comment
==================================================
Pancake
pancakes
Paris
partial
party
5 rows found
And if the user then types ‘r’, add it to the begins() search:
SQL>select distinct comment from ix_wstable_comments_ws
SQL&where begins(comment, 'par')
SQL&fetch first 10 rows only;
comment
==================================================
Paris
partial
party
3 rows found
Let’s say the user now picks ‘partial’ from the current list of suggested words. Use the builtin.match_word() function to find comments containing that word:
SQL>select * from wstable where builtin.match_word(comment, 'partial');
id comment
=========== ==================================================
2 What is a partial lunar eclipse?
1 row found
Or, if the user does not choose any of the suggested words, and instead just enters ‘par’, use builtin.begins_word() to find comments having a word that starts with ‘par’:
SQL>select * from wstable where builtin.begins_word(comment, 'par');
id comment
=========== ==================================================
2 What is a partial lunar eclipse?
5 Pancake party in Paris
2 rows found
As you now have seen, implementing word search with autocomplete using Mimer SQL is easy. And of course autocomplete when inserting data can be implemented in a similar way.
