1

I want to search inside a full search column using certain letters, I mean:

select "Name","Country","_score" from datatable where match("Country", 'China');

Returns many rows and is ok. My question is, how can I search for example:

select "Name","Country","_score" from datatable where match("Country", 'Ch');

I want to see, China, Chile, etc.

I think that match_type phrase_prefix can be the answer, but I don't know how I can use (correct syntax).

Jonny C
  • 1,943
  • 3
  • 20
  • 36
chan go
  • 137
  • 11

2 Answers2

1

The match predicate supports different types by use of using match_type [with (match_parameter = [value])].

So in your example using the phrase_prefix match type:

select "Name","Country","_score" from datatable where match("Country", 'Ch') using phrase_prefix;

gives you your desired results.

See the match predicate documentation: https://crate.io/docs/en/latest/sql/fulltext.html?#match-predicate

Sebastian Utz
  • 719
  • 3
  • 9
0

If you just need to match the beginning of a string column, you don't need a fulltext analyzed column. You can use the LIKE operator instead, e.g.:

cr> create table names_table (name string, country string);
CREATE OK (0.840 sec)

cr> insert into names_table (name, country) values ('foo', 'China'), ('bar','Chile'), ('foobar', 'Austria');
INSERT OK, 3 rows affected (0.049 sec)

cr> select * from names_table where country like 'Ch%';
+---------+------+
| country | name |
+---------+------+
| Chile   | bar  |
| China   | foo  |
+---------+------+
SELECT 2 rows in set (0.037 sec)
Christian
  • 279
  • 2
  • 8
  • I want to use a very long column (more than 100000 records, so, the fulltest analyzed looks like good. Thanks for the suggestion. – chan go May 08 '15 at 12:01