TEXT Queries

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.

For more information about this, please refer to “TEXT Indexing Elements”.

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.