This chapter describes queries that use Tibero TEXT functions.
CATSEARCH
The CATSEARCH function is used in a WHERE clause of a SELECT statement to use CTXCAT indexes.
Usage
CATSEARCH( column, text_query, reserved argument)
Item
Description
column
Column that has a CTXCAT index.
text_query
CATSEARCH query operation, which defines the text to search for.
reserved argument
Reserved for future functionality.
CATSEARCH query operations
The CATSEARCH query supports the following six operations.
Operation
Syntax Example
Description
Logical AND
a b c
Searches for rows that include a, b, and c.
Logical OR
a | b | c
Searches for rows that include a, b, or c.
Logical NOT
a - b
Searches for rows that include a but do not include b.
Cannot be used alone.
" "
"a b c"
Searches for rows that include a phrase "a b c".
( )
(a b) | c
Specifies the priority of operations.
In the example, a b is calculated first, and then | c is calculated.
Wildcard
ab*, a*b, *ab
* represents zero or more arbitrary characters.
Return Values
Return values are NUMBERs that have no meaning.
Examples
The following example creates a table and a CTXCAT index.
SQL> create table book(id number, name varchar(4000));
SQL> create index book_ctxcatindex on book(name) ctxcatindex;
The following example inserts data to the table.
SQL> insert into book values(1,'The little boat.');
SQL> insert into book values(2,'The little yellow digger.');
SQL> insert into book values(3,'The magic pasta pot : an old tale.');
SQL> insert into book values(4,'The mousehole cat.');
SQL> insert into book values(5,'The pear in the pear tree.');
SQL> insert into book values(6,'The rainbow fish.');
SQL> insert into book values(7,'The story about Ping.');
The following example searches for data.
SQL> select * from book where catsearch(name, 'story ping', null) = 0;
ID NAME
------- --------------
7 The story about Ping.
1 row selected.
SQL> select * from book where catsearch(name, 'li*', null) = 0;
ID NAME
------- --------------
1 The little boat.
2 The little yellow digger.
2 rows selected.
SQL> select * from book where catsearch(name, '"little boat"', null) = 0;
ID NAME
------- --------------
1 The little boat.
1 row selected.
CONTAINS
The CONTAINS function is used in a WHERE clause of a SELECT statement to use CONTEXT indexes.
Usage
CONTAINS( column, text_query, reserved argument)
Item
Description
column
Column that has a CONTEXT index.
text_query
CONTAINS query operation, which defines the text to search for.
reserved argument
Reserved for future functionality.다.
CONTAINS query operations
The CONTAINS query supports the following six operations.
Operation
Syntax Example
Description
Logical AND
a AND b
Searches for rows that include a, b, and c.
Logical OR
a OR b, a | b
Searches for rows that include a or b.
Logical NOT
a ~ b
Searches for rows that include a but do not include b.
SEQUENCE
a b
Searches for rows that include the phrase of a b.
Wildcard
ab%, %ab
% represents zero or more arbitrary characters.
WITHIN
a WITHIN SENTENCE, a WITHIN PARAGRAPH
Searches for rows containing a sentence or paragraph that includes a.
Return Values
Return values are NUMBERs that have no meaning.
Examples
The following example creates a table and a CONTEXT index and inserts data to the table.
SQL> create table t(c1 clob);
SQL> insert into t values('Paris Rome Warsav');
SQL> insert into t values('Seoul Incheon');
SQL> insert into t values('Paris Rome Seoul');
SQL> commit;
SQL> create index t_idx on t(c1) indextype is ctxsys.context;
The following example searches for data.
SQL> select * from t where contains(c1, 'Paris', NULL) > 0;
C1
------------------------------------------------------------------
Paris Rome Warsav
Paris Rome Seoul
2 rows selected.
SQL> select * from t where contains(c1, 'Paris and Warsav', NULL) > 0;
C1
-------------------------------------------------------------------
Paris Rome Warsav
1 row selected.
SQL> select * from t where contains(C1, 'Warsav | Seoul', null) = 0;
C1
---------------------------------------------------------------------
Paris Rome Warsav
Seoul Incheon
Paris Rome Seoul
3 rows selected.
SQL> select * from t where contains(c1, 'Paris - Seoul', NULL) > 0;
C1
---------------------------------------------------------------------
Paris Rome Warsav
Paris Rome Seoul
2 rows selected.
The following example searches for data by using the WITHIN operator. WHITESPACE must be set for the LEXER property.
SQL> drop table t;
Table 'T' dropped.
SQL> create table t (c1 clob);
Table 'T' created.
SQL> insert into t values ('Paris.-Rome');
1 row inserted.
SQL> commit;
Commit completed.
SQL> select * from t;
C1
--------------------------------------------------------------------------
Paris.-Rome
1 row selected.
SQL> begin
2 text_ddl.create_preference('mylex','BASIC_LEXER');
3 text_ddl.set_attribute('mylex','WHITESPACE','-');
4 end;
5 /
PSM completed.
SQL> begin
2 text_ddl.create_section_group('nullgroup', 'NULL_SECTION_GROUP');
3 text_ddl.add_special_section('nullgroup', 'SENTENCE');
4 end;
5 /
PSM completed.
SQL> create index t_idx on t (a) indextype is ctxsys.context
2 parameters ('LEXER mylex sync (on commit) section group nullgroup');
Index 'T_IDX' created.
SQL> select * from t where contains(a, 'Paris Rome within sentence') > 0;
0 row selected.
SQL> select * from t where contains(a, 'Paris within sentence') > 0;
C1
-------------------------------------------------------------------------------
Paris.-Rome
1 row selected.
