How to use FULL SEARCH TEXT in H2 database?

Question

Consider the following example.

CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init"; CALL FT_INIT(); DROP TABLE IF EXISTS TEST; CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR); INSERT INTO TEST VALUES(1, 'Hello World'); CALL FT_CREATE_INDEX('PUBLIC', 'TEST', NULL); 

and I performed the following request

 SELECT * FROM FT_SEARCH('Hello', 0, 0); 

But this query returns "PUBLIC". "TEST" WHERE "ID" = 1 .

• Do I have to execute this "PUBLIC"."TEST" WHERE "ID"=1 again to get an entry containing the word “Hello”?
• What is the query to search all records with "ell" in them from FT_Search. e.g. like% ell% in H2 Full Full Text Search

Answer 1

• Yes, each row in a query using FT_SEARCH represents a row-table-diagram in which one of the keywords was found. The search is case insensitive, and the text parameter FT_SEARCH may contain more than one word. For example,

   DELETE FROM TEST; INSERT INTO TEST VALUES(1, 'Hello World'); INSERT INTO TEST VALUES(2, 'Goodbye World'); INSERT INTO TEST VALUES(3, 'Hello Goodbye'); CALL FT_REINDEX(); SELECT * FROM FT_SEARCH('hello goodbye', 0, 0); 

  returns only string three:

   QUERY SCORE "PUBLIC"."TEST" WHERE "ID"=3 1.0 

  Also note that FT_SEARCH_DATA can be used to retrieve the data itself. For example,

   SELECT T.* FROM FT_SEARCH_DATA('hello', 0, 0) FT, TEST T WHERE FT.TABLE='TEST' AND T.ID=FT.KEYS[0]; 

  returns both lines containing the keyword:

   ID NAME 1 Hello World 3 Hello Goodbye 

• Apache Lucene supports wildcard searches, although leading wildcards (like * ell) tend to be expensive.

Answer 2

Should I do this "PUBLIC" again. "TEST" WHERE "ID" = 1 to get an entry containing the word "Hello"?

Yes, unless you use a connection as described in trashgod. The reason is that usually strings are much more than two words. For example, a string contains a CLOB with a document. If the result of a full-text search contains data, then a full-text search will be much slower.

What is the query to search for all records with the text "ell" in them from FT_Search. e.g.% ell% in full-blown full-text H2 search

Native full-text search cannot do this directly. The reason is that the full text of the search indexes whole words. (By the way, does Google support search if you know only part of the word? Apache Lucene really supports it). Actually for H2 there would be a way: first, find the word table (FT.WORDS) for matches, and then use a regular search.